Types of Joins

The ANSI SQL standard defines five types of joins that we’ll discuss.

1. Cross Join : In this case, we pick the first row of Table A and match it with every row of Table B.

2. Inner Join : In case of an inner join, a condition, or multiple conditions, are tested to determine if a row from Table A should be joined with a row from Table B.

3. Left Outer Join : all rows from the left are always included in the result set and rows from the right are only included if they match the join predicate.

4. Right Outer Join : The right join is the reverse of the left join.

5. Full Outer Join : In the case of a full join, rows from both the tables are included in the result set. Rows that evaluate true for the join predicate are only included once. Rows that don’t match the predicate have NULL inserted for columns belonging to the other table. Note that MySQL doesn’t support a full join.

• Self Join : A self join is the result set when a table is joined to itself.

Self Inner Join

SELECT * FROM table1 INNER JOIN table1 ON join condition;

Each row will join with every other row of the second table. The total number of rows in the resulting table will be 121 because the table has 11 rows.

SELECT * FROM Actors a INNER JOIN Actors b;

SELECT * FROM Actors a INNER JOIN Actors b USING(FirstName);

The USING clause defines one or more columns that are in both tables or results and used to join or match rows.

Inner Join

SELECT * FROM table1 INNER JOIN table2 ON join condition;

SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
INNER JOIN DigitalAssets  
ON Actors.Id = DigitalAssets.ActorID;

If the two tables had the same column name for the actor’s ID then we could have used the alternative syntax with USING clause to make the query slightly less verbose as shown below:

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors 
INNER JOIN DigitalAssets 
USING(Id);

• Note that the columns listed in the SELECT clause are unique across the two tables. However, if the two tables had columns with the same names then we would need to disambiguate the two by fully qualifying the column with the table name.

• It’s not necessary to use the INNER JOIN clause to get an inner join between two tables. We can also use the WHERE clause to achieve the same effect as shown below:

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors, DigitalAssets 
WHERE ActorId=Id;

There’s no difference in using the WHERE clause or the INNER JOIN clause in query performance, rather it is just a matter of taste.

• We can also create a cartesian product between the two tables as we did in the self join section.

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors, DigitalAssets;

Or,

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors 
INNER JOIN DigitalAssets;

Union

The UNION clause allows us to combine the results from several queries together. The clause doesn’t join the table but merely clubs the two results together.

Query1 UNION Query2

(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name" 
FROM Actors 
ORDER BY NetworthInMillions 
DESC LIMIT 2)

UNION

(SELECT CONCAT(FirstName, ' ', SecondName) AS "ThisAliasIsIgnored" 
FROM Actors 
ORDER BY NetworthInMillions 
ASC LIMIT 2);

• When using the UNION clause, the two result sets being combined should have the same number and order of columns.

SELECT FirstName, Id FROM Actors UNION SELECT FirstName, null FROM Actors;

SELECT MaritalStatus FROM Actors UNION SELECT Gender FROM Actors;

Note that the union clause doesn’t output duplicate values and works similarly to the distinct clause. If we want duplicate values to be included in the query result, we need to use the UNION ALL clause as follows:

SELECT MaritalStatus FROM Actors UNION ALLSELECT Gender FROM Actors;

• Another quirk of the UNION clause is that it may ignore the ORDER BY clause when used without the LIMIT clause in a subquery.

(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name"  
FROM Actors  
ORDER BY NetworthInMillions DESC  LIMIT 2)  

UNION  

(SELECT NetworthInMillions 
FROM Actors 
ORDER BY NetworthInMillions ASC);

The above code is wrong & doesn't work properly. The below code is a modification

(SELECT CONCAT(FirstName, ' ', SecondName) AS "Actor Name"  
FROM Actors  
ORDER BY NetworthInMillions DESC  LIMIT 2  

UNION  

(SELECT NetworthInMillions 
FROM Actors 
ORDER BY NetworthInMillions ASC LIMIT 3);

Left & Right Outer Joins

SELECT * FROM table1 LEFT [OUTER] JOIN table2 ON join condition

SELECT * FROM table1 RIGHT [OUTER] JOIN table2 ON join condition

SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
LEFT JOIN DigitalAssets  
ON Actors.Id = DigitalAssets.ActorID;

SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
RIGHT JOIN DigitalAssets  
ON Actors.Id = DigitalAssets.ActorID;

• that an alternative syntax for left and right joins is LEFT OUTER JOIN and RIGHT OUTER JOIN respectively, though there’s no difference in functionality if you skip the OUTER keyword.

Natural Join

The clause attempts to find the natural join between participating tables by matching on columns with same name.

SELECT * FROM table1 NATURAL JOIN table2

SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
NATURAL JOIN DigitalAssets;

Note that since none of the columns in the two tables share the same name, the result is a cartesian product.

The same result can be achieved using the inner join as follows:

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors 
INNER JOIN DigitalAssets;

---

-- Alter the column name
ALTER TABLE DigitalAssets CHANGE ActorId Id INT;
-- rerun the previous query
SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
NATURAL JOIN DigitalAssets;

Same as:

SELECT FirstName, SecondName, AssetType, URL 
FROM Actors 
INNER JOIN DigitalAssets 
USING (Id);

---

SELECT FirstName, SecondName, AssetType, URL
FROM Actors 
NATURAL LEFT OUTER JOIN DigitalAssets;