In a relational database, information is usually stored in multiple tables rather than one large table.
For example, a university may maintain separate tables for:
Sometimes we need information from more than one table.
For example:
PostgreSQL provides JOINs to retrieve related information from multiple tables.
A JOIN is used to combine related rows from two or more tables based on a common column.
Consider these two tables:
students
| student_id | student_name | department_id |
|---|---|---|
| 1 | Ali | 101 |
| 2 | Sara | 102 |
| 3 | Ahmed | 101 |
| 4 | Ayesha | 103 |
departments
| department_id | department_name |
|---|---|
| 101 | Computer Science |
| 102 | Mathematics |
| 103 | Physics |
The department_id column is common to both tables.
Using JOIN, we can combine student information with department information.
First, create the departments table.
CREATE TABLE departments (
department_id INTEGER PRIMARY KEY,
department_name VARCHAR(100)
);
Insert department records:
INSERT INTO departments
(department_id, department_name)
VALUES
(101, 'Computer Science'),
(102, 'Mathematics'),
(103, 'Physics'),
(104, 'Chemistry');
Now create the students table.
CREATE TABLE students (
student_id INTEGER PRIMARY KEY,
student_name VARCHAR(100),
department_id INTEGER,
age INTEGER,
FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
Insert student records:
INSERT INTO students
(student_id, student_name, department_id, age)
VALUES
(1, 'Ali', 101, 20),
(2, 'Sara', 102, 21),
(3, 'Ahmed', 101, 22),
(4, 'Ayesha', 103, 23);
Notice that the Chemistry department (104) has no students.
PostgreSQL supports several types of JOINs.
| JOIN | Purpose |
|---|---|
| INNER JOIN | Returns matching records from both tables. |
| LEFT JOIN | Returns all records from the left table and matching records from the right table. |
| RIGHT JOIN | Returns all records from the right table and matching records from the left table. |
| FULL OUTER JOIN | Returns all records from both tables, matching where possible. |
| CROSS JOIN | Returns every possible combination of rows from both tables. |
An INNER JOIN returns only those records that have matching values in both tables.
Syntax:
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
Example: Display student names with department names.
SELECT
students.student_name,
departments.department_name
FROM students
INNER JOIN departments
ON students.department_id = departments.department_id;
Output:
| student_name | department_name |
|---|---|
| Ali | Computer Science |
| Sara | Mathematics |
| Ahmed | Computer Science |
| Ayesha | Physics |
Explanation:
FROM students selects the first table.INNER JOIN departments joins the departments table.ON specifies the matching condition.The Chemistry department does not appear because it has no matching student.
A LEFT JOIN returns:
NULL values when no matching record exists in the right table.Syntax:
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example: Display all departments with their students.
SELECT
departments.department_name,
students.student_name
FROM departments
LEFT JOIN students
ON departments.department_id = students.department_id;
Output:
| department_name | student_name |
|---|---|
| Computer Science | Ali |
| Computer Science | Ahmed |
| Mathematics | Sara |
| Physics | Ayesha |
| Chemistry | NULL |
Explanation:
NULL for its student name.A RIGHT JOIN returns:
NULL values when no matching record exists in the left table.Example: Display all departments with their students using RIGHT JOIN.
SELECT
departments.department_name,
students.student_name
FROM students
RIGHT JOIN departments
ON students.department_id = departments.department_id;
The result includes all departments, including Chemistry, even though it has no students.
Important: The right table is the table written after RIGHT JOIN.
A FULL OUTER JOIN returns all records from both tables.
It includes:
Example:
SELECT
students.student_name,
departments.department_name
FROM students
FULL OUTER JOIN departments
ON students.department_id = departments.department_id;
In our sample data, all students have matching departments, but Chemistry has no students. Therefore, Chemistry appears with a NULL student name.
If either table has unmatched records, those records also appear in the result.
A CROSS JOIN returns every possible combination of rows from two tables.
This is also called the Cartesian Product.
Syntax:
SELECT columns
FROM table1
CROSS JOIN table2;
Example:
SELECT
students.student_name,
departments.department_name
FROM students
CROSS JOIN departments;
We have:
The result contains:
\[ 4 \times 4 = 16 \text{ rows} \]
Explanation: Each student is combined with every department, regardless of the department ID.
A table alias is a short temporary name given to a table inside a query.
It makes SQL queries shorter and easier to read.
Example:
SELECT
s.student_name,
d.department_name
FROM students AS s
INNER JOIN departments AS d
ON s.department_id = d.department_id;
Here:
s is the alias for students.d is the alias for departments.We can use these aliases instead of writing full table names.
We can combine JOIN with the WHERE clause to filter the joined results.
Example: Display students belonging to the Computer Science department.
SELECT
s.student_name,
d.department_name
FROM students AS s
INNER JOIN departments AS d
ON s.department_id = d.department_id
WHERE d.department_name = 'Computer Science';
Output:
| student_name | department_name |
|---|---|
| Ali | Computer Science |
| Ahmed | Computer Science |
We can use ORDER BY to sort the results of a JOIN query.
Example: Display students and their departments in alphabetical order of student names.
SELECT
s.student_name,
d.department_name
FROM students AS s
INNER JOIN departments AS d
ON s.department_id = d.department_id
ORDER BY s.student_name ASC;
| JOIN Type | What does it return? |
|---|---|
| INNER JOIN | Only matching records from both tables |
| LEFT JOIN | All left-table records and matching right-table records |
| RIGHT JOIN | All right-table records and matching left-table records |
| FULL OUTER JOIN | All records from both tables |
| CROSS JOIN | Every possible combination of rows |
INNER JOIN
Only the overlapping records.
LEFT JOIN
All of A and matching records from B.
RIGHT JOIN
All of B and matching records from A.
FULL OUTER JOIN
All records from both A and B.
Green areas represent the records included in each JOIN. CROSS JOIN is different: it creates every possible row combination.
department_id, specify the table or alias, such as s.department_id.Use the students and departments tables created in this lesson.
Beginner Level
Intermediate Level
Hint for Exercise 9: Use LEFT JOIN, COUNT(), and GROUP BY.
Advanced Level
Hint for Exercise 13: Use LEFT JOIN with WHERE students.student_id IS NULL.
Remember these basic JOIN patterns:
-- INNER JOIN
SELECT *
FROM students s
INNER JOIN departments d
ON s.department_id = d.department_id;
-- LEFT JOIN
SELECT *
FROM students s
LEFT JOIN departments d
ON s.department_id = d.department_id;
-- RIGHT JOIN
SELECT *
FROM students s
RIGHT JOIN departments d
ON s.department_id = d.department_id;
-- FULL OUTER JOIN
SELECT *
FROM students s
FULL OUTER JOIN departments d
ON s.department_id = d.department_id;
-- CROSS JOIN
SELECT *
FROM students s
CROSS JOIN departments d;
SELF JOIN in PostgreSQL