SQL Joins – Syntax and Examples

SQL Joins

SQL JOIN statements are used to combine rows from two or more tables based on related columns between them. Joins are essential when working with relational databases, allowing data from different tables to be merged and retrieved in a meaningful way.

The main types of SQL joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

In this tutorial, we will go through SQL Join statements, types of joins, their syntax, and how to use joins in SQL statements, with the help of well detailed examples.

---

Syntax of SQL Joins

The basic syntax for each type of SQL join is:

-- INNER JOIN Syntax
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

-- LEFT JOIN Syntax
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

-- RIGHT JOIN Syntax
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

-- FULL JOIN Syntax
SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Each type of join retrieves data in a unique way:

• INNER JOIN: Retrieves records with matching values in both tables.
• LEFT JOIN: Retrieves all records from the left table and matching records from the right table; returns NULL for non-matching rows from the right table.
• RIGHT JOIN: Retrieves all records from the right table and matching records from the left table; returns NULL for non-matching rows from the left table.
• FULL JOIN: Retrieves all records where there is a match in either the left or right table; NULL values are returned for non-matching rows.

Step-by-Step Examples with MySQL

We’ll go through examples demonstrating each type of join using MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use sample students and grades tables with fields student_id, name, age, and grade.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    grade CHAR(1),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age)
VALUES
('Alice', 14),
('Bob', 15),
('Charlie', 14);

INSERT INTO grades (student_id, subject, grade)
VALUES
(1, 'Math', 'A'),
(2, 'Math', 'B'),
(3, 'Science', 'A'),
(1, 'Science', 'B');

---

Examples: Using Joins in Queries

Now, let’s explore different scenarios of using SQL joins with these tables.

---

1. Using INNER JOIN to Find Matching Records

To retrieve students with grades in at least one subject:

SELECT students.name, grades.subject, grades.grade
FROM students
INNER JOIN grades
ON students.student_id = grades.student_id;

This query returns students with a record in the grades table, displaying only those with matching student IDs in both tables.

---

2. Using LEFT JOIN to Include All Students

To retrieve all students and their grades, including those without any grades:

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades
ON students.student_id = grades.student_id;

This query returns all students, displaying NULL for students without matching records in the grades table.

---

3. Using RIGHT JOIN to Include All Grades

To retrieve all grades and the respective student names, even if some grades don’t have a matching student record:

SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades
ON students.student_id = grades.student_id;

This query returns all grades, displaying NULL for any grades without a matching student record in the students table.

---

4. Using FULL JOIN for Complete Set of Records

To retrieve all students and grades, including all records from both tables regardless of match (note: MySQL doesn’t natively support FULL JOIN, so this requires a LEFT JOIN and RIGHT JOIN with UNION):

SELECT students.name, grades.subject, grades.grade
FROM students
LEFT JOIN grades ON students.student_id = grades.student_id
UNION
SELECT students.name, grades.subject, grades.grade
FROM students
RIGHT JOIN grades ON students.student_id = grades.student_id;

This query combines both LEFT JOIN and RIGHT JOIN results, showing all records from both tables with NULL where no match exists.

Reference: SQL UNION

---

FAQs for SQL Joins

1. What does the SQL JOIN clause do?

The JOIN clause combines records from two or more tables based on a related column, allowing you to retrieve data across multiple tables.

2. What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only matching rows, while LEFT JOIN includes all rows from the left table and matches from the right table, showing NULL for non-matching rows from the right table.

3. Can I use multiple joins in a single query?

Yes, you can use multiple joins in a single query to combine data from several tables, specifying each join type and condition.

4. Does SQL support FULL JOIN in MySQL?

MySQL does not directly support FULL JOIN, but you can achieve it by combining LEFT JOIN and RIGHT JOIN with UNION.

5. How does RIGHT JOIN differ from LEFT JOIN?

RIGHT JOIN includes all rows from the right table and matches from the left table, showing NULL for non-matching rows from the left table, while LEFT JOIN includes all rows from the left table and matches from the right table.