4. Joins
Note
🔹 COALESCE() Function in SQL — Simplest Explanation
What it does:
Returns the first non-NULL value from the list of arguments.
🧠 Syntax:
COALESCE(value1, value2, ..., valueN)
It checks values one by one and returns the first one that is NOT NULL.
✅ Example:
SELECT COALESCE(NULL, NULL, 'Hello', 'World');
🔸 Output: 'Hello'
Because the first two are NULL, and 'Hello' is the first non-NULL value.
💡 Real-life example with a table:
| student_id | nickname | full_name |
|---|---|---|
| 1 | NULL | Alice |
| 2 | Bob | Robert |
| 3 | NULL | NULL |
SELECT student_id, COALESCE(nickname, full_name, 'No Name') AS display_name
FROM students;
Result:
| student_id | display_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | No Name |
🔁 Use cases:
- Show fallback values (e.g., "Not Found", "Unknown")
- Avoid NULLs in output
- Create default values in reports
- how do we do full outer join in sql we do it by doing union of left join and right join so we get both simlar and non similar data grouped
JOINS in SQL (Simplest Explanation with Examples)
A JOIN is used in SQL to combine rows from two or more tables, based on a related column between them.
1. INNER JOIN
- Returns only matching rows from both tables.
SELECT *
FROM Employees
INNER JOIN Departments ON Employees.DeptID = Departments.ID;
🧠 Think: Only people with a valid department.
2. LEFT JOIN (or LEFT OUTER JOIN)
- Returns all rows from the left table, and matching rows from the right table.
- If no match, shows NULL for right table.
SELECT *
FROM Employees
LEFT JOIN Departments ON Employees.DeptID = Departments.ID;
🧠 Think: All employees, even if their department is missing.
3. RIGHT JOIN (or RIGHT OUTER JOIN)
- Returns all rows from the right table, and matching rows from the left table.
SELECT *
FROM Employees
RIGHT JOIN Departments ON Employees.DeptID = Departments.ID;
🧠 Think: All departments, even if they have no employees.
4. FULL OUTER JOIN
- Returns all rows from both tables.
- NULL where there is no match on either side.
SELECT *
FROM Employees
FULL OUTER JOIN Departments ON Employees.DeptID = Departments.ID;
🧠 Think: Everything, matched or not.
5. CROSS JOIN
- Returns every possible combination (cartesian product).
SELECT *
FROM Employees
CROSS JOIN Departments;
🧠 Think: Every employee with every department.
Example Tables:
Employees
| ID | Name | DeptID |
|---|---|---|
| 1 | John | 10 |
| 2 | Alice | 20 |
| 3 | Bob | NULL |
Departments
| ID | DeptName |
|---|---|
| 10 | HR |
| 30 | Engineering |
INNER JOIN Result:
| Name | DeptName |
|---|---|
| John | HR |
LEFT JOIN Result:
| Name | DeptName |
|---|---|
| John | HR |
| Alice | NULL |
| Bob | NULL |
RIGHT JOIN Result:
| Name | DeptName |
|---|---|
| John | HR |
| NULL | Engineering |
FULL OUTER JOIN Result:
| Name | DeptName |
|---|---|
| John | HR |
| Alice | NULL |
| Bob | NULL |
| NULL | Engineering |
mysql> show tables;
+--------------------+
| Tables_in_practice |
+--------------------+
| departments |
| students |
+--------------------+
2 rows in set (0.00 sec)
mysql> select * from departments;
+---------+------------------+
| dept_id | dept_name |
+---------+------------------+
| 1 | Computer Science |
| 2 | Mathematics |
| 3 | Physics |
| 4 | Chemistry |
+---------+------------------+
4 rows in set (0.02 sec)
mysql> select * from students;
+------------+---------+---------+
| student_id | name | dept_id |
+------------+---------+---------+
| 101 | Alice | 1 |
| 102 | Bob | 2 |
| 103 | Charlie | 3 |
| 104 | David | NULL |
| 105 | Eva | 5 |
+------------+---------+---------+
5 rows in set (0.01 sec)
mysql> select * from students s
-> cross join departments d
-> on s.dept_id=d.dept_id ;
+------------+---------+---------+---------+------------------+
| student_id | name | dept_id | dept_id | dept_name |
+------------+---------+---------+---------+------------------+
| 101 | Alice | 1 | 1 | Computer Science |
| 102 | Bob | 2 | 2 | Mathematics |
| 103 | Charlie | 3 | 3 | Physics |
+------------+---------+---------+---------+------------------+
3 rows in set (0.00 sec)
mysql> select * from students s
-> join departments d
-> on s.dept_id=d.dept_id ;
+------------+---------+---------+---------+------------------+
| student_id | name | dept_id | dept_id | dept_name |
+------------+---------+---------+---------+------------------+
| 101 | Alice | 1 | 1 | Computer Science |
| 102 | Bob | 2 | 2 | Mathematics |
| 103 | Charlie | 3 | 3 | Physics |
+------------+---------+---------+---------+------------------+
3 rows in set (0.00 sec)
mysql> select * from students s
-> left join departments d
-> on s.dept_id=d.dept_id ;
+------------+---------+---------+---------+------------------+
| student_id | name | dept_id | dept_id | dept_name |
+------------+---------+---------+---------+------------------+
| 101 | Alice | 1 | 1 | Computer Science |
| 102 | Bob | 2 | 2 | Mathematics |
| 103 | Charlie | 3 | 3 | Physics |
| 104 | David | NULL | NULL | NULL |
| 105 | Eva | 5 | NULL | NULL |
+------------+---------+---------+---------+------------------+
5 rows in set (0.00 sec)
mysql> select * from students s
-> right join departments d
-> on s.dept_id=d.dept_id ;
+------------+---------+---------+---------+------------------+
| student_id | name | dept_id | dept_id | dept_name |
+------------+---------+---------+---------+------------------+
| 101 | Alice | 1 | 1 | Computer Science |
| 102 | Bob | 2 | 2 | Mathematics |
| 103 | Charlie | 3 | 3 | Physics |
| NULL | NULL | NULL | 4 | Chemistry |
+------------+---------+---------+---------+------------------+
4 rows in set (0.00 sec)
mysql> select * from students s
-> union
-> select * from departments;
ERROR 1222 (21000): The used SELECT statements have a different number of columns