Understanding MySQL Joins

An SQL query walks into a bar and sees two tables. He walks up to them and asks “Can I join you?”

— Source: Unknown

JOINS

CREATE TABLE orders (
order_id INT NOT NULL,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
PRIMARY KEY(order_id)
)
CREATE TABLE customers (
customer_id INT NOT NULL,
customer_name VARCHAR(50),
country VARCHAR(10),
PRIMARY KEY(customer_id)
);
customers table
orders table

INNER JOIN

Venn Diagram of INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers INNER JOIN orders on customers.customer_id= orders.customer_id;

OUTER JOIN

LEFT JOIN

Venn Diagram of LEFT JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id;

LEFT JOIN EXCLUDING INNER JOIN

Venn Diagram of LEFT JOIN EXCLUDING INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id where orders.customer_id IS NULL;

RIGHT JOIN

Venn Diagram of RIGHT JOIN

SELECT  customers.customer_id, customer_name, country, order_id from orders RIGHT JOIN customers on orders.customer_id= customers.customer_id;

RIGHT JOIN EXCLUDING INNER JOIN

Venn Diagram of RIGHT JOIN EXCLUDING INNER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from orders RIGHT JOIN customers on orders.customer_id= customers.customer_id where orders.customer_id IS NULL;

FULL OUTER JOIN using LEFT JOIN and RIGHT JOIN

Venn Diagram of FULL OUTER JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers LEFT JOIN orders on customers.customer_id= orders.customer_id
UNION
SELECT customers.customer_id, customer_name, country, order_id from customers RIGHT JOIN orders on customers.customer_id= orders.customer_id;

CROSS JOIN

Venn Diagram of CROSS JOIN

SELECT  customers.customer_id, customer_name, country, order_id from customers CROSS JOIN orders;

References

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store