JOIN

INNER JOIN is used to select/join related values from two or more tables. In summary, the SQL INNER JOIN statement is used to join tables with a common value.

SELECT c.customer_id, first_name, amount, payment_date
FROM customer as c INNER JOIN payment p ON p.customer_id=
c.customer_id ORDER BY payment_date DESC;

Two tables are included in the query. It is a query that compares the customer_id value in the customer table with the customer_id values in the payment table for the same value and returns the customer_id , first_name , amount and payment_date values.

With “customer as c”, the customer table is nicknamed ‘c’.

c.customer_id = customer.customer_id alias is given to shorten the query.

The alias ‘p’ is given to the payment table with “payment p”.

The USING clause can be used because both tables have the same customer_id column.

SELECT customer_id, first_name, last_name, amount, payment_date
FROM customer INNER JOIN payment USING(customer_id) ORDER BY
payment_date;

SELECT c.customer_id, c.first_name customer_first_name, c.last_
name customer_last_name, s.first_name staff_first_name, s.last_
name staff_last_name, amount, payment_date FROM customer c INNER
JOIN payment p ON p.customer_id = c.customer_id INNER JOIN staffs 
ON p.staff_id = s.staff_id ORDER BY payment_date;

SELECT inventory.film_id FROM rental INNER JOIN inventory ON
inventory.inventory_id = rental.inventory_id WHERE return_date
BETWEEN ‘2005-05-29’ AND ‘2005-05-30’;

Left join

It is used to select/merge all rows from left table (table1) and right table (table2) specified by condition. If the value in the left table and the value in the right table do not match, the values in the right table take the value ‘NULL’.

SELECT column_names FROM table1 LEFT JOIN table2 ON table1.column_
name = table2.column_name WHERE conditions;

SELECT film.film_id, title, inventory_id FROM film LEFT JOIN
inventory ON inventory.film_id = film.film_id ORDER BY title;

Each row in the film table can have zero or many rows in the inventory table. Each row in the inventory table has only one row in the film table. The film_id column establishes the link between the film and the inventory tables.

SELECT film.film_id, film.title, inventory_id FROM film LEFT JOIN
inventory ON inventory.film_id = film.film_id WHERE inventory.
film_id IS NULL ORDER BY title ;

If both tables have the same column name used in the ON clause, you can use the USING expression like this:

SELECT f.film_id, title, inventory_id FROM film f LEFT JOIN
inventory i USING (film_id) WHERE i.film_id IS NULL ORDER
BY title;

Self join

Self join is the process of joining a table with itself. There is a reference relationship between the rows in the table.

Example: The query returns film’s title values, which are of the same length.

SELECT f1.title, f2.title, f1.length FROM film f1
INNER JOIN film f2 ON f1.film_id <> f2.film_id
AND f1.length = f2.length;