SQL Joins and Subqueries Explained Step-by-Step

SQL Joins and Subqueries are powerful tools for combining and filtering data across multiple tables. This guide will help you understand how they work with syntax, use cases, and examples.

---

Topic 5: SQL Joins

SQL Joins are used to combine rows from two or more tables based on a related column.

1. INNER JOIN

Returns records that have matching values in both tables.

SELECT Employees.Name, Departments.DeptName
FROM Employees
INNER JOIN Departments
ON Employees.DeptID = Departments.ID;

2. LEFT JOIN (LEFT OUTER JOIN)

Returns all records from the left table and the matched records from the right table. If no match, NULLs are returned.

SELECT Customers.Name, Orders.OrderDate
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

3. RIGHT JOIN (RIGHT OUTER JOIN)

Opposite of LEFT JOIN. Returns all rows from the right table and matched rows from the left table.

4. FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables. Unmatched rows show NULLs.

5. SELF JOIN

A self join is a regular join but the table is joined with itself.

SELECT A.Name AS Employee, B.Name AS Manager
FROM Employees A
JOIN Employees B
ON A.ManagerID = B.EmployeeID;

6. CROSS JOIN

Returns the Cartesian product of both tables.

---

Topic 6: SQL Subqueries

A subquery is a query inside another query. It's used to return data that will be used in the main query.

1. Nested Subquery

Used inside WHERE or HAVING clause.

SELECT Name
FROM Employees
WHERE DeptID = (
  SELECT ID
  FROM Departments
  WHERE DeptName = 'Sales'
);

2. Correlated Subquery

References column from outer query and runs for each row.

SELECT Name
FROM Employees E
WHERE Salary > (
  SELECT AVG(Salary)
  FROM Employees
  WHERE DeptID = E.DeptID
);

3. Subquery in FROM clause

SELECT Dept, AVG_Salary
FROM (
  SELECT DeptID AS Dept, AVG(Salary) AS AVG_Salary
  FROM Employees
  GROUP BY DeptID
) AS Sub;

---

Why Use Joins and Subqueries?

• Combine data from multiple tables
• Simplify complex queries
• Improve reporting and data analysis

Also read: More SQL tutorials on Sabbir93s Blog

FAQs

• Q: What is the difference between INNER JOIN and LEFT JOIN?
  A: INNER JOIN shows only matched rows; LEFT JOIN shows all left table rows even without matches.
• Q: When should I use a subquery?
  A: When you need to filter data using values calculated from another query.
• Q: Are joins faster than subqueries?
  A: Usually yes, but it depends on the database engine and indexing.

Labels: SQL Joins, SQL Subqueries, SQL Tutorial, SQL Syntax