Day 6: Joins

JOINS in SQL are used to combine information from multiple tables in one query.

INNER JOIN

In order to understand inner joins and joins in general, we have two picture two tables, table A and table B. The two tables must have a common column. We call this a reference column.
For composite keys i.e. primary keys made up of a combination of two columns, the reference column must also be created from the two columns

INNER JOIN combines two tables using a reference column and returns matching rows in BOTH tables.

-- Syntax
SELECT * FROM TableA A JOIN TableB B ON A.coulmn1 = B.column2

1. We can use an alias A to refer to TableA and an alias B to refer to TableB

2. The name INNER in the INNER JOIN is optional

3. A.coulmn1 and B.column2 are reference columns

The JOIN function happens as a whole. We therefore filter using the WHERE clause after the JOIN is complete.

Two tables may have same column names. To differentiate the two, use the table name e.g. SELECT A.name FROM Person A JOIN Employee E ON E.id = A.id

A.name represents name column from table A while B.name represent name column from the table B

FULL OUTER JOIN

FULL OUTER JOIN returns matched and unmatched rows from both tables (it's an union of both). If there is no match, the missing side will contain null

-- Syntax
SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.name = TableB.name;

JOINS & WHERE

A little detour - We’ve talked a little about JOINS. But how exactly do we filter data. This where the WHERE clause comes in.

SELECT * FROM tableA 
FULL OUTER JOIN tableB ON tableA.column1 = tableB.column1 
WHERE tableB.col2 IS NULL

LEFT JOIN

A LEFT OUTER JOIN returns all rows from the left table (TableA) with the matching rows from the right table (TableB) or null – if there is no match in the right table

-- Syntax
SELECT * FROM TableA 
LEFT OUTER JOIN TableB ON tableA.name = tableB.name;

RIGHT JOIN

A RIGHT OUTER JOIN returns all rows from the right table (TableB) with the matching rows from the left table (TableA) or null – if there is no match in the left table.
Think of it as the result we would get if we used LEFT JOIN and then swapped TableA with TableB

Joins on multiple conditions

Remember we’ve mentioned composite key? Now, there are certain tables where a single column is not enough to make a primary key. For a row to be unique, it must use two or more columns.
A composite key is a primary key that is made up of more than one column to uniquely identify records in a table.

In search instances, we use the AND keyword to combine columns

SELECT * FROM TableA A
INNER JOIN TableB B ON A.column1 = B.column1 
AND
A.column2 = B.column2