In partnership with

Find out why 1M+ professionals read Superhuman AI daily.

In 2 years you will be working for AI

Or an AI will be working for you

Here's how you can future-proof yourself:

1. Join the Superhuman AI newsletter – read by 1M+ people at top companies

2. Master AI tools, tutorials, and news in just 3 minutes a day

3. Become 10X more productive using AI

Join 1,000,000+ pros at companies like Google, Meta, and Amazon that are using AI to get ahead.

Sign up and start learning AI

SQL JOINS

SQL JOINS help us combine data from two tables based on a common column.

Let’s suppose we have Two Tables:

Employees Table (A)

Departments Table (B)

1️⃣ INNER JOIN → Only Matching Records

✅ Returns only employees who have a matching department.

SELECT A.name, B.dept_name   FROM Employees A   INNER JOIN Departments B ON A.dept_id = B.dept_id; 

Result:

📝 Charlie (no department) and Finance (no employee) are removed.

2️⃣ LEFT JOIN → All Employees, Even If No Department

✅ Returns all employees and their department (NULL if no match).

SELECT A.name, B.dept_name   FROM Employees A   LEFT JOIN Departments B ON A.dept_id = B.dept_id; 

Result:

📝 Charlie stays, but his department is NULL.

3️⃣ RIGHT JOIN → All Departments, Even If No Employee

✅ Returns all departments and their employees (NULL if no match).

SELECT A.name, B.dept_name   FROM Employees A   RIGHT JOIN Departments B ON A.dept_id = B.dept_id; 

Result:

📝 Finance stays, but no employee is assigned.

4️⃣ FULL OUTER JOIN → Everything

✅ Returns all employees and all departments (NULL where no match).

SELECT A.name, B.dept_name FROM Employees A FULL OUTER JOIN Departments B ON A.dept_id = B.dept_id;

Result:

📝 Charlie (no department) and Finance (no employee) are included.

5️⃣ SELF JOIN → Compare a Table to Itself

✅ Used when data relates to itself, like employees and managers.

Employees Table (Again)

Find Employees and Their Managers

SELECT e.name AS Employee, m.name AS Manager   FROM Employees e   LEFT JOIN Employees m ON e.manager_id = m.emp_id; 

Result:

📝 Alice has no manager, but Bob and Charlie report to Alice.

Summary

Final Notes:

• Use INNER JOIN when you only need matches.

• Use LEFT JOIN when the left table is the focus.

• Use RIGHT JOIN when the right table is the focus.

• Use FULL OUTER JOIN when you need everything.

• Use SELF JOIN when comparing within a single table (e.g., employee-manager).

A Simple Example to understand LEFT JOIN

Suppose you have two tables…

Question: Find out of the name of all employees and their departments. And also employees who do not belong to any department

Employees Table (A)

Departments Table (B)

SQL Query (LEFT JOIN)

SELECT A.name, B.dept_name   FROM Employees A   
LEFT JOIN Departments B   
ON A.dept_id = B.dept_id; 

Result:

Explanation:

✅ All employees are included.
 ✅ Matching departments are shown.
 ✅ Charlie has NULL because there's no match in the Departments table.

Key Rule for LEFT JOIN

• All rows from the left table (Employees) stay.

• Only matching rows from the right table (Departments) appear.

• If there’s no match, NULL is placed in right table columns.

LEETCODE QUESTION

1068. Product Sales Analysis 

DIFFICULTY LEVEL: EASY

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

The result format is in the following example.

Output Example 1:

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+
Explanation: 
From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008.
From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009.
From sale_id = 7, we can co

From above we can see that sales table does not have all the information we need in output…

SO, we need to join these tables so that we can have a resultant table with everything we need.

Initially, we need to understand what type of join we need to apply in order to get this does correctly.

For this, we can look for primary key and foreign key.

Primary Key 

• A Primary Key is a column (or set of columns) in a table that uniquely identifies each row.

• It cannot be NULL and must be unique.

• Example: In a Students table, the student_id can be a primary key because each student has a unique ID.

🔗 Foreign Key (Think: Link between tables)

• A Foreign Key is a column in one table that refers to the Primary Key in another table.

• It creates a relationship between the two tables.

• Example: In a Courses table, student_id could be a foreign key that connects each course to a student in the Students table.

Now as you can that that product_id column in product table is primary key. And foreign key in the Sales table.

In this case, we need to retain the primary key, so we can use Left join by keeping the Product table in the left hand side…

Here is the code:

Final Output: 
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+

Thanks for your time and consideration

Shailesh and NextCareerStep team!