JOINS - rFronteddu/general_wiki GitHub Wiki

• JOINS allows us to combine information from multiple tables.
• The main reason for the different JOIN types is to decide how to deal with information only present in one of the joined tables.

INNER JOINS

An INNER JOIN will result with the set of records that match in both tables (A∩B).

SELECT * FROM table_a
INNER JOIN table_b
ON table_a.col_match = table_b.col_match

SELECT reg_id, logins.name, log_id
FROM registrations
INNER JOIN logins
ON registrations.name = logins.name

SELECT film.title, first_name, last_name FROM film
INNER JOIN film_actor ON (film.film_id = film_actor.film_id)
INNER JOIN actor ON (actor.actor_id = film_actor.actor_id)
WHERE first_name='Nick' AND last_name='Wahlberg'

OUTER JOINS

OUTER JOINS allow us to specify how to deal with values only present in one of the tables being joined.

FULL JOINS

Full Outer JOIN

Grab everything regardless of match (A∪B). Fill missing values with null.

SELECT * FROM table_a
FULL OUTER JOIN table_b
ON table_a.col_match = table_b.col_match

FULL OUTER JOIN can be qualified with a WHERE statement to get rows unique to either table (union without intersection).

SELECT * FROM table_a
FULL OUTER JOIN table_b
ON table_a.col_match = table_b.col_match
WHERE table_a.id IS null OR table_b.id IS null

A+A∩B (extra A elements are filled with null for B data)

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

AuB - B

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

AuB - A∩B

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

Left Outer JOIN

• Results in the set of records that are in the left table, if there is no match with the right table, the results are null (A + intersection).
• Note that the order here matters.
• It either belongs to both table or to the left one

SELECT * FROM table_a
LEFT OUTER JOIN table_b
ON table_a.col_match=table_b.col_match;

We can get things unique to table_a doing:

SELECT * FROM table_a
LEFT OUTER JOIN table_b
ON table_a.col_match=table_b.col_match
WHERE table_b.id IS null;

Right Outer JOIN

• It either belongs to both table or to the right one exclusively.

`` SELECT * FROM table_a RIGHT OUTER JOIN table_b ON table_a.col_match = table_b.col_match

* We can add a clarifying statement here too
``
SELECT * FROM table_a
RIGHT OUTER JOIN table_b
ON table_a.col_match = table_b.col_match
WHERE table_a.id IS null

UNIONS

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

• Results should stack on top of each other

SELECT column_name(s) FROM table_1
UNION
SELECT column_name(s) FROM table_2

SELF-JOIN

A self-join is a query in which a table is joined to itself. They are useful for comparing values in a column of rows within the same table.

We can do self-join using the JOIN command and we must use an alias for the table, otherwise the table names would be ambiguous.

SELECT table_a.col, table_b.col
FROM table AS table_a
JOIN table AS table_b ON table_a.some_col=table_b.other_col

Assume to want to match employee name and their reports recipient name

SELECT emp.name, report.name AS rep
FROM employees AS emp
JOIN employees AS report ON
emp.emp_id = report.report_id

Find films with same length

SELECT f1.title, f2.title, f1.length 
FROM film as f1
JOIN film AS f2 ON 
f1.film_id != f2.film_id AND f1.length = f2.length