SQL JOINs Explained for Beginners: INNER, LEFT, RIGHT, FULL

# What is a JOIN? A JOIN combines two tables into one result, by matching up the rows that belong together. Why you need it: a database keeps each kind of data in its own table. The orders are in orders, the names are in customers. To answer "who placed each order?", you need both tables at once. - `JOIN` brings a second table into the query - `ON` the rule that says which rows belong together: an order's customer_id is a customer's id - `customers.name` the name column of the customers table Picture each table as a circle. A JOIN keeps the overlap: the orders and customers that match. Run the query to see where this level ends up: every order next to the name of the customer who placed it. The next three slides build it step by step. > In an interview, "what is a JOIN?" wants one sentence: it combines rows from two tables using a column they share.

SELECT orders.id, customers.name
FROM orders
JOIN customers
  ON customers.id =
    orders.customer_id

# Step 1: an order only has a number Take one order from that result: order 6. In the orders table, order 6 doesn't store a name. It stores customer_id 4: the number of the customer who placed it. - `customer_id` the id of the customer who placed the order > Storing the id instead of the name keeps each customer's details in one place. If Dev changes their email, you fix one row in customers, not every order.

SELECT id, customer_id, qty
FROM orders
WHERE id = 6

# Step 2: look the number up by hand The name is in the customers table. Find the row with id 4 there: Dev Patel. You just matched customer_id in orders to id in customers. A JOIN does exactly this, for every order at once. > This match, orders.customer_id = customers.id, is the foreign key from the keys level. Most JOINs follow a foreign key.

SELECT id, name
FROM customers
WHERE id = 4

# Step 3: JOIN does it for every row - `FROM orders` start with the orders - `JOIN customers` bring in the customers table - `ON customers.id = orders.customer_id` the matching rule - `customers.name` the name column of the customers table Every match becomes one row of the result. > Read a JOIN from its ON first. It tells you how the two tables connect; the rest only says which columns to show.

SELECT customers.name, orders.id
FROM orders
JOIN customers
  ON customers.id =
    orders.customer_id

# Table nicknames Typing the full table names gets long, so give each table a short nickname. - `FROM orders o` call orders o in this query - `JOIN customers c` call customers c - `AS` optional: FROM orders AS o means the same > Pick nicknames that match the table: o for orders, c for customers. Real queries join four or five tables, and t1, t2, t3 quickly become unreadable.

SELECT c.name, o.id
FROM orders o
JOIN customers c
  ON c.id = o.customer_id

# Ambiguous columns Both tables have a column called id. Write just id and the database can't tell which one you mean, so it returns an error. - `o.id` the order's id - `c.id` the customer's id > Put the nickname in front of every column in a JOIN, even the ones only one table has. If someone later adds a name column to orders, your query keeps working.

SELECT id, name
FROM orders o
JOIN customers c
  ON c.id = o.customer_id

# One row per match A JOIN returns one row for every matching pair, not one row per customer. Alice has 3 orders, so she appears 3 times. That's why customers JOIN orders gives 12 rows, not 7. > If a JOIN gives more rows than you expected, count the matches. It's the most common reason a total comes out doubled.

SELECT c.name, o.id, o.status
FROM customers c
JOIN orders o
  ON o.customer_id = c.id
WHERE c.name = 'Alice Brown'

# LEFT JOIN: keep rows without a match JOIN drops customers with no orders, so George isn't in the answer. LEFT JOIN keeps every row of the first table and puts NULL where the match should be. - `INNER JOIN` the full name of JOIN: only matches - `LEFT JOIN` every row of the first table, matched or not > Use LEFT JOIN when the question says "all" or "every": every customer and their orders. Use JOIN when it says "only those with".

SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id

# Find rows with no match After a LEFT JOIN, a row with no match has NULL on the other side. Keep only those rows to find who never ordered: George. - `WHERE o.id IS NULL` keep only customers with no order > Test a column that is never empty when there is a match, like the id. This pattern answers "who never ordered?" and "which pizza never sold?", both classic interview questions.

SELECT c.name
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
WHERE o.id IS NULL

# The picture: two circles Draw each table as a circle. Where they overlap are the rows that match. - `JOIN` only the overlap - `LEFT JOIN` the whole left circle - `RIGHT JOIN` the whole right circle - `FULL OUTER JOIN` both circles - `WHERE B.key IS NULL` after a LEFT JOIN: only the part of A with no match, the way you found George The sheet shows all seven, each with its query. A is the table after FROM, B is the one you join. > The circles show which rows are kept, not how many come back: one customer can match many orders.

SELECT customers.name, orders.id
FROM customers
LEFT JOIN orders
  ON orders.customer_id =
    customers.id

# RIGHT JOIN RIGHT JOIN keeps every row of the second table. It is a LEFT JOIN with the tables swapped. > Most teams never write RIGHT JOIN. Swap the tables and write LEFT JOIN: queries are easier to read when they all go the same way.

SELECT c.name, o.id
FROM orders o
RIGHT JOIN customers c
  ON c.id = o.customer_id

# Stack two results: UNION A JOIN puts tables side by side. UNION puts the rows of one SELECT under the rows of another, so customers and staff come back as one list. - `UNION` the second SELECT's rows go under the first's - `'customer' AS kind` the same text in every row, to show where each row came from Both SELECTs need the same number of columns, in the same order. The column names come from the first SELECT. > UNION matches columns by position, not by name. SELECT name, city … UNION SELECT city, name … mixes names and cities in one column without any error.

SELECT name, 'customer' AS kind
FROM customers
UNION
SELECT name, 'staff' FROM staff

# UNION or UNION ALL Alice ordered pizzas 1, 5 and 3. The staff favourites are 5, 6, 2, 5 and 1. - `UNION` removes repeated rows, like DISTINCT: 5 rows - `UNION ALL` keeps every row: 8 rows here - `ORDER BY` at the very end sorts the whole result, both halves together Change UNION ALL to UNION and run it again. > UNION ALL is faster, because it doesn't look for repeats. Use it when the two halves can't overlap, or when you want the repeats counted.

SELECT pizza_id
FROM orders
WHERE customer_id = 1
UNION ALL
SELECT favourite_pizza_id
FROM staff
WHERE favourite_pizza_id IS NOT NULL
ORDER BY 1

# FULL OUTER JOIN FULL OUTER JOIN keeps the rows without a match from both tables. Priya has no favourite pizza, and four pizzas are nobody's favourite: they all come back, with NULLs. > MySQL and MariaDB (what Appian Cloud runs on) don't have FULL OUTER JOIN. There you write a LEFT JOIN, then UNION, then a RIGHT JOIN.

SELECT s.name AS staff,
  p.name AS favourite
FROM staff s
FULL OUTER JOIN pizzas p
  ON p.id = s.favourite_pizza_id

# CROSS JOIN: every combination CROSS JOIN has no ON. It pairs every row with every row of the other table: 7 customers × 8 pizzas = 56 rows. > In MySQL a JOIN with no ON works like a CROSS JOIN. If a result suddenly has thousands of rows, check that every JOIN has its ON.

SELECT c.name, p.name
FROM customers c
CROSS JOIN pizzas p

# SELF JOIN: a table joined to itself Each staff member's manager_id holds another staff member's id. So you use the staff table twice, with two nicknames. - `staff s` the staff member - `staff m` their manager > Gina has no manager, so a JOIN leaves her out. Use LEFT JOIN when everyone must appear.

SELECT s.name AS staff,
  m.name AS manager
FROM staff s
JOIN staff m ON m.id = s.manager_id

# Joining three tables Add one JOIN per table. Each JOIN gets its own ON. > Start from the table the question is about (here, orders). Join one more table at a time, and run the query after each JOIN to check the row count.

SELECT c.name, p.name, o.qty
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
JOIN pizzas p ON p.id = o.pizza_id

# Counting with a JOIN How many orders does each customer have? Join, then GROUP BY the customer. - `COUNT(o.id)` counts the matched orders, so George gets 0 - `COUNT(*)` counts rows, so George's NULL row counts as 1 > With a LEFT JOIN, count a column of the right-hand table, never *.

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.id
GROUP BY c.name

# Which JOIN will you use? - `INNER JOIN` all the time: only matches - `LEFT JOIN` all the time: everything from the first table - `SELF JOIN` when a table points at itself - `CROSS JOIN` now and then, to build combinations - `RIGHT JOIN` almost never - `FULL OUTER JOIN` hardly ever, and not in MySQL > In Appian, record type relationships do most joins for you. You'll write JOINs yourself in database views and report queries.