There are some more complex operations you can do with a join.
Connect two tables, returning only rows in tableA without a match in tableB
SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.foreign_key = tableB.primary_key WHERE tableB.primary_key IS NULL
We can connect a table to itself. This typically requires understanding how to use AS, which lets us rename one of the tables. Below is an example Employee table.
| emp_pk | boss_fk | name |
|---|---|---|
| 1 | 0 | Sarah |
| 2 | 1 | Bill |
| 3 | 1 | Sam |
This returns a list of people with their boss. Note that use of re-naming fields and tables. We rename the 2nd time empl is used, marking it as the boss table. We then join the employee’s foreign key for their boss with the primary key of the boss table.
SELECT boss., empl. FROM empl LEFT OUTER JOIN empl AS boss ON empl.boss_fk = boss.emp_pk
There are multiple ways that tables can connect:
It’s possible to have an entire SQL query as a portion of another query. See the example below. It performs some filtering on staff before using it in the join.
SELECT * FROM sales INNER JOIN ( SELECT id FROM staff WHERE id > 10 ) staffOver10 ON sales.employeeID = staffOver10.id
A full join returns all rows from either side of a join. We don’t typically use these in our class.
A cross join returns every row on each side for every row on the other side. It’s rarely used. We could theoretically use it to join a table of years (2000, 2001, 2002, …) and months (Jan, Feb, …) to get a list of every month for every year.