Joins in SQL
Joins Introduction
Inner join Left join Right join Cross join
Tables
Members Table
+-----------+--------+
| member_id | name |
+-----------+--------+
| 1 | John |
| 2 | Jane |
| 3 | Mary |
| 4 | David |
| 5 | Amelia |
+-----------+--------+
Committees Tables
+--------------+--------+
| committee_id | name |
+--------------+--------+
| 1 | John |
| 2 | Mary |
| 3 | Amelia |
| 4 | Joe |
+--------------+--------+
Inner Join
SELECT * FROM members
INNER JOIN committees ON committees.name = members.name;
Output:
+-----------+--------+--------------+--------+
| member_id | name | committee_id | name |
+-----------+--------+--------------+--------+
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
+-----------+--------+--------------+--------+
We can see that the return table is of people who are in both the members and committees table, which are John, Mary, and Amelia.
Left Join
For each row in the left table, the left joins compares with every row in the right table. Will include al rows from the left table, if there isn’t a match in the right table the VALUE NULL will be inplace.
SELECT *
FROM members
LEFT JOIN Committees ON Committees.name = Members.name;
Output:
+-----------+--------+--------------+--------+
| member_id | name | committee_id | name |
+-----------+--------+--------------+--------+
| 1 | John | 1 | John |
| 2 | Jane | NULL | NULL |
| 3 | Mary | 2 | Mary |
| 4 | David | NULL | NULL |
| 5 | Amelia | 3 | Amelia |
+-----------+--------+--------------+--------+
We can see that the VAlUE NULL is placed in for rows that don’t have a match in the right table. We can see that Joe isn’t in the table because we don’t include values from the right table that don’t have a match in the Left Table.
Right Join
Pretty much the opposite of a Left Join, we incude all values from the Right Table and any matching values from the Left table.
SELECT *
FROM members
RIGHT JOIN Committees ON Committees.name = members.name;
Output:
+-----------+--------+--------------+--------+
| member_id | name | committee_id | name |
+-----------+--------+--------------+--------+
| 1 | John | 1 | John |
| 3 | Mary | 2 | Mary |
| 5 | Amelia | 3 | Amelia |
| NULL | NULL | 4 | Joe |
+-----------+--------+--------------+--------+
From the results table, we can see that we have all 4 names from the committee table John, Mary, Amelia and Joe. We have the matching name and id if they’re in the left table,
Cross Join
Unlike the Inner, Left, and Right Joins, the Cross Join doesn’t have a join condition.
The cross join makes a Cartesian product of rows from the joined tables. The cross join combines each row from the first table with every row from the right table to make the result set.
Suppose the first table has n rows and the second table has m rows. The cross join that joins the tables will return nxm rows.
Syntax:
SELECT Columns FROM Table1 CROSS JOIN Table2;
This example uses the cross join clause to join the members with the committees tables:
SELECT *
FROM members
CROSS JOIN committees;
Output:
+-----------+--------+--------------+--------+
| member_id | name | committee_id | name |
+-----------+--------+--------------+--------+
| 1 | John | 4 | Joe |
| 1 | John | 3 | Amelia |
| 1 | John | 2 | Mary |
| 1 | John | 1 | John |
| 2 | Jane | 4 | Joe |
| 2 | Jane | 3 | Amelia |
| 2 | Jane | 2 | Mary |
| 2 | Jane | 1 | John |
| 3 | Mary | 4 | Joe |
| 3 | Mary | 3 | Amelia |
| 3 | Mary | 2 | Mary |
| 3 | Mary | 1 | John |
| 4 | David | 4 | Joe |
| 4 | David | 3 | Amelia |
| 4 | David | 2 | Mary |
| 4 | David | 1 | John |
| 5 | Amelia | 4 | Joe |
| 5 | Amelia | 3 | Amelia |
| 5 | Amelia | 2 | Mary |
| 5 | Amelia | 1 | John |
+-----------+--------+--------------+--------+
Full Joins
- Not compatable in mySQL
But joins everythign into one big table, kinda a cluster F*** if you ask me.