Skip to content

Advanced Joins & Set Operations

Complete your join toolkit with RIGHT JOIN, UNION, and multi-table modifications.

INNER JOIN and LEFT JOIN cover most real-world cases. But sometimes you need the "other side" of a join, or you need to combine results from unrelated queries. This stage fills in the remaining pieces.

Definition

RIGHT JOIN returns all rows from the right table and matching rows from the left table. UNION combines the result sets of two SELECT statements into a single result set.

Required step: If you do not have these tables yet, run this setup in the playground before continuing. It creates the tables used by every example in this stage.

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE IF NOT EXISTS students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

CREATE TABLE IF NOT EXISTS enrollments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  score DECIMAL(5,2),
  FOREIGN KEY (student_id) REFERENCES students(id)
);

CREATE TABLE IF NOT EXISTS courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS archived_students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE IF NOT EXISTS waitlist (
  id INT AUTO_INCREMENT PRIMARY KEY,
  course_name VARCHAR(100) NOT NULL
);

INSERT INTO students (name, email) VALUES
  ('Alice', 'alice@school.edu'),
  ('Bob', 'bob@school.edu'),
  ('Charlie', 'charlie@school.edu');

INSERT INTO enrollments (student_id, course_name, score) VALUES
  (1, 'Math', 90), (1, 'Science', 85), (2, 'Math', 78);

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table and matching rows from the left table. Unmatched left rows get NULL.

RIGHT JOIN

SELECT s.name, e.course_name
FROM students s
RIGHT JOIN enrollments e ON s.id = e.student_id;

Returns all enrollments, even if the student does not exist (unusual but possible with orphaned data). LEFT JOIN with tables swapped is usually more readable.

Convention:RIGHT JOIN vs LEFT JOIN

RIGHT JOIN is rarely used in practice. Most developers prefer LEFT JOIN and write the tables in a different order. The result is the same, but LEFT JOIN reads more naturally: "start with this table, then join that one."

Example: FROM students s LEFT JOIN enrollments e is the same as FROM enrollments e RIGHT JOIN students s.

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables: every row from the first table combined with every row from the second. If table A has 3 rows and table B has 4 rows, the result has 12 rows.

CROSS JOIN

SELECT s.name, c.name AS course_name
FROM students s
CROSS JOIN courses c;

Pairs every student with every course. If there are 5 students and 4 courses, the result has 20 rows. Useful for generating combinations.

Use cases:When to use CROSS JOIN

Generating all combinations. Pair every student with every course for a scheduling matrix.

Time series. Cross join a dates table with a products table to find missing data points.

Be careful: CROSS JOINs produce large result sets. Always use WHERE to filter the result unless you truly need every combination.

UNION and UNION ALL

UNION combines the result of two SELECT statements into a single result set. Both queries must return the same number of columns with compatible types.

UNION ALL (keeps duplicates)

SELECT name FROM students
UNION ALL
SELECT name FROM archived_students;

Combines names from both tables. UNION ALL keeps duplicates, which is faster. Use this when you know there are no duplicates or you do not care.

UNION (removes duplicates)

SELECT course_name FROM enrollments
UNION
SELECT course_name FROM waitlist;

Combines course names from both tables and removes duplicates. UNION sorts and deduplicates, which is slower than UNION ALL.

Performance:UNION vs UNION ALL

UNION ALL is faster because it just concatenates the results. Use it when duplicates do not matter or when you know the data is unique.

UNION adds a deduplication step. Use it only when you need unique results. If you need sorting, add ORDER BY at the end (applied to the final result).

UPDATE with JOIN

You can update rows in one table based on values in another table using JOIN in an UPDATE statement. This is very common in applications.

UPDATE with JOIN

UPDATE students s
INNER JOIN enrollments e ON s.id = e.student_id
SET s.email = CONCAT(s.name, '@school.edu')
WHERE e.course_name = 'Math';

Updates the email of students enrolled in Math. The JOIN links students to their enrollments, and the WHERE filters to Math students only.

UPDATE with LEFT JOIN

UPDATE students s
LEFT JOIN enrollments e ON s.id = e.student_id
SET s.email = CONCAT(s.name, '@alumni.edu')
WHERE e.student_id IS NULL;

Updates email for students with no enrollments (LEFT JOIN + IS NULL pattern). This finds students not enrolled in any course.

Multiple-Table DELETE

MySQL supports deleting rows from multiple tables in a single statement using JOIN. This is faster than separate DELETE statements.

DELETE with JOIN

DELETE s FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
WHERE e.course_name = 'History';

Deletes students enrolled in History. The 's' after DELETE specifies which table to delete from. Only students are deleted, not enrollments.

DELETE with LEFT JOIN

DELETE s FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
WHERE e.student_id IS NULL;

Deletes students with no enrollments. The LEFT JOIN + IS NULL pattern finds students not referenced in enrollments.

Warning: Multi-table DELETE is MySQL-specific syntax. The standard SQL way uses a subquery. If portability matters, use DELETE with a subquery instead.

What is the difference between UNION and UNION ALL?

In DELETE s FROM students s LEFT JOIN enrollments e ..., what does the 's' after DELETE mean?

Key Takeaways

  • RIGHT JOIN returns all rows from the right table; LEFT JOIN is preferred for readability.
  • UNION ALL concatenates results (fast). UNION deduplicates (slower).
  • UPDATE ... JOIN lets you update one table based on another table's data.
  • DELETE with JOIN removes rows from a specific table using another table for filtering.
  • Multi-table UPDATE and DELETE are MySQL-specific; standard SQL uses subqueries.

Ready to test your knowledge?

Take a Quiz

Related Tutorials