LEFT JOIN and RIGHT JOIN in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does a LEFT JOIN do in SQL?

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If there is no match, the result will contain NULL for columns from the right table.

beginner

What is the difference between LEFT JOIN and RIGHT JOIN?

LEFT JOIN returns all rows from the left table and matching rows from the right table. RIGHT JOIN returns all rows from the right table and matching rows from the left table.

beginner

In a RIGHT JOIN, what happens if there is no matching row in the left table?

If there is no matching row in the left table, the result will still include the row from the right table, but columns from the left table will be NULL.

intermediate

Write a simple SQL query using LEFT JOIN to combine two tables: employees and departments.

SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id;

intermediate

Why might you use a LEFT JOIN instead of an INNER JOIN?

You use LEFT JOIN when you want to keep all rows from the left table even if there is no matching row in the right table. INNER JOIN only returns rows with matches in both tables.

What will a LEFT JOIN return if there is no matching row in the right table?

A All rows from the right table

B No rows

C Only rows with matching keys

D All rows from the left table with NULLs for right table columns

LEFT JOIN returns all rows from the left table and fills NULL for right table columns if no match exists.

Which JOIN returns all rows from the right table and matching rows from the left table?

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D FULL JOIN

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

If you want to keep all employees even if they don't belong to a department, which JOIN should you use?

A LEFT JOIN

B RIGHT JOIN

C INNER JOIN

D CROSS JOIN

LEFT JOIN keeps all rows from the left table (employees) even if no matching department exists.

What will happen if you use RIGHT JOIN but the right table has no rows?

A Result will be empty

B All rows from left table returned

C All rows from right table returned

D Error occurs

RIGHT JOIN returns all rows from the right table; if right table is empty, result is empty.

Which JOIN type can be thought of as the opposite of LEFT JOIN?

A INNER JOIN

B FULL JOIN

C RIGHT JOIN

D CROSS JOIN

RIGHT JOIN is the opposite of LEFT JOIN, returning all rows from the right table.

Explain in your own words how LEFT JOIN works and when you would use it.

Think about keeping all your friends in a list even if some don't have phone numbers.

Describe the difference between LEFT JOIN and RIGHT JOIN with an example.

Imagine two lists: one is your friends, the other is their phone numbers.

Practice

1. What does a LEFT JOIN do in PostgreSQL?

easy

A. Returns all rows from the left table and matching rows from the right table, or NULL if no match.

B. Returns all rows from the right table and matching rows from the left table, or NULL if no match.

C. Returns only rows that have matching values in both tables.

D. Returns all rows from both tables, matching where possible.

Solution

1. Step 1: Understand LEFT JOIN behavior

   A LEFT JOIN returns all rows from the left table regardless of matches in the right table.

2. Step 2: Check what happens with unmatched rows

   If there is no matching row in the right table, the result includes NULLs for right table columns.

3. Final Answer:

   Returns all rows from the left table and matching rows from the right table, or NULL if no match. -> Option A

4. Quick Check:

   LEFT JOIN = all left rows + matched right rows [OK]

Hint: LEFT JOIN keeps all left rows, fills right with NULL if no match [OK]

Common Mistakes:

• Confusing LEFT JOIN with RIGHT JOIN
• Thinking LEFT JOIN returns only matched rows
• Assuming unmatched rows are dropped

2. Which of the following is the correct syntax for a RIGHT JOIN in PostgreSQL?

easy

A. SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

B. SELECT * FROM table1 JOIN RIGHT table2 ON table1.id = table2.id;

C. SELECT * FROM table1 RIGHT OUTER JOIN table2 WHERE table1.id = table2.id;

D. SELECT * FROM table1 RIGHT JOIN ON table1.id = table2.id table2;

Solution

1. Step 1: Recall correct RIGHT JOIN syntax

   The correct syntax is: SELECT ... FROM left_table RIGHT JOIN right_table ON condition;

2. Step 2: Check each option

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; matches the correct syntax exactly. Options B, C, and D have syntax errors or misplaced keywords.

3. Final Answer:

   SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id; -> Option A

4. Quick Check:

   RIGHT JOIN syntax = FROM left_table RIGHT JOIN right_table ON condition [OK]

Hint: RIGHT JOIN syntax: FROM left_table RIGHT JOIN right_table ON condition [OK]

Common Mistakes:

• Placing JOIN keyword incorrectly
• Using WHERE instead of ON for join condition
• Misordering table names

3. Given these tables:

employees
id | name
1 | Alice
2 | Bob
3 | Carol

departments
id | dept_name
2 | Sales
3 | HR
4 | IT

What is the result of this query?

SELECT employees.name, departments.dept_name FROM employees LEFT JOIN departments ON employees.id = departments.id ORDER BY employees.id;

medium

A. [{"name": "Alice", "dept_name": "Sales"}, {"name": "Bob", "dept_name": "HR"}, {"name": "Carol", "dept_name": "IT"}]

B. [{"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}, {"name": null, "dept_name": "IT"}]

C. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}]

D. [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": null}, {"name": "Carol", "dept_name": null}]

Solution

1. Step 1: Understand LEFT JOIN with given tables

   LEFT JOIN keeps all employees. For each employee, it tries to find a department with matching id.

2. Step 2: Match rows and fill NULLs

   Alice (id=1) has no matching department, so dept_name is NULL. Bob (id=2) matches Sales. Carol (id=3) matches HR.

3. Final Answer:

   [{"name": "Alice", "dept_name": null}, {"name": "Bob", "dept_name": "Sales"}, {"name": "Carol", "dept_name": "HR"}] -> Option C

4. Quick Check:

   LEFT JOIN keeps all employees, unmatched dept_name is NULL [OK]

Hint: LEFT JOIN keeps all left rows; unmatched right columns are NULL [OK]

Common Mistakes:

• Assuming unmatched rows are excluded
• Mixing up LEFT and RIGHT JOIN results
• Ignoring NULL values for unmatched rows

4. You wrote this query but it gives an error:

SELECT * FROM employees RIGHT JOIN departments employees.id = departments.id;

What is the error and how to fix it?

medium

A. RIGHT JOIN is not supported; use LEFT JOIN instead

B. Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id

C. Table names are reversed; switch employees and departments

D. Use WHERE instead of ON for join condition

Solution

1. Step 1: Identify syntax error in JOIN clause

   The query is missing the ON keyword before the join condition, which is required syntax.

2. Step 2: Correct the syntax

   Add ON before employees.id = departments.id to fix the error.

3. Final Answer:

   Missing ON keyword before join condition; fix by adding ON before employees.id = departments.id -> Option B

4. Quick Check:

   JOIN condition must use ON keyword [OK]

Hint: JOIN conditions always need ON keyword before condition [OK]

Common Mistakes:

• Omitting ON keyword
• Using WHERE instead of ON for join condition
• Confusing table order in JOIN

5. You want to list all departments and their employees, including departments with no employees. Which query achieves this?

hard

A. SELECT departments.dept_name, employees.name FROM employees RIGHT JOIN departments WHERE employees.id = departments.id;

B. SELECT departments.dept_name, employees.name FROM employees LEFT JOIN departments ON employees.id = departments.id;

C. SELECT departments.dept_name, employees.name FROM departments RIGHT JOIN employees ON departments.id = employees.id;

D. SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id;

Solution

1. Step 1: Identify which table to keep all rows from

   We want all departments, even those without employees, so departments is the left table in LEFT JOIN.

2. Step 2: Use LEFT JOIN from departments to employees

   LEFT JOIN keeps all departments and adds matching employees or NULL if none.

3. Final Answer:

   SELECT departments.dept_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.id; -> Option D

4. Quick Check:

   LEFT JOIN from departments keeps all departments [OK]

Hint: LEFT JOIN from main table keeps all its rows, unmatched get NULLs [OK]

Common Mistakes:

• Using employees as left table instead of departments
• Using RIGHT JOIN incorrectly
• Confusing which table to keep all rows from