Union & Join

Union

• The UNION operator is used to combine the result-set of two or more SELECT statements.

• Every SELECT statement within UNION must have the same number of columns

• The columns must also have similar data types

• The UNION ALL can include the duplicated value

SELECT column_name(s) FROM table1
UNION (ALL)
SELECT column_name(s) FROM table2;

Join

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

Inner Join

• Only return the result matched to the condition

Left Join

• Return all the result of left table and right table matched to the condition

Right Join

• Return all the result of right table and left table matched to the condition

Full Join

• Return all the result of left table and right table

Vs Subquery

// Orders (OrderID, CustomerID, OrderDate);
// Customers (CustomerID, CustomerName, ContactName, Country);

// Join
SELECT * from Customers JOIN Orders 
ON Orders.CustomerID=Customers.CustomerID;

//Sub-query
select * from Customers c WHERE c.CustomerID = ANY(SELECT CustomerID from Orders)

• Subquery can express sql query in logical order and easier to understand

• Join execution time and performance is better