SQL Joins

SQL Joins – Combining Tables for Richer Insights

In previous blogs, we learned how to retrieve, filter, and summarize data. But real-world databases usually have multiple tables, like employees, departments, sales, and customers. To get meaningful insights, you need to combine tables, and that’s where SQL joins come in.

In this blog, we’ll explore the different types of joins with practical examples.

1. What Is a SQL Join?

A join allows you to query data from two or more tables based on a related column.

Common example: You have an employees table and a departments table. To see employee names along with their department names, you need a join.

2. INNER JOIN

Returns rows that match in both tables.

Example: Employees with Their Departments

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

  • Only shows employees who are assigned to a department.

  • ON specifies the matching column.

first_name       last_name          department_name
John                    Doe          Sales
Jane       Smith          IT

3. LEFT JOIN (or LEFT OUTER JOIN)

Returns all rows from the left table and matching rows from the right table.
If there’s no match, NULL is returned.

Example: All Employees and Their Departments

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id;

  • Includes employees without a department.

first_name              last_name              department_name
John              Doe              Sales
Mike              Ross              NULL

4. RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all rows from the right table and matching rows from the left table.

Example: All Departments and Their Employees

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d
ON e.department_id = d.department_id;

  • Includes departments with no employees assigned.

. FULL OUTER JOIN

Returns all rows from both tables, matching when possible, and NULL when there’s no match.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON e.department_id = d.department_id;

  • Shows all employees and all departments, matched where possible.

6. CROSS JOIN

Returns all possible combinations of rows from both tables (Cartesian product).
Use with caution – can produce very large results.

SELECT e.first_name, d.department_name
FROM employees e
CROSS JOIN departments d;

  • Every employee paired with every department.

7. Tips for Using Joins

  • Always specify the matching column with ON.

  • Use aliases (e for employees, d for departments) for readability.

  • Joins can be combined with WHERE, GROUP BY, and ORDER BY for powerful queries.

SQL joins are essential for combining related data across multiple tables. With joins, you can:

  • See richer insights about employees, customers, or sales.

  • Handle missing data with LEFT, RIGHT, or FULL OUTER JOIN.

  • Build complex queries for reporting and analytics.

Comments

Post a Comment

Popular posts from this blog

Database Integration in FastAPI (SQLAlchemy CRUD)

  Introduction So far, we’ve built APIs without storing data.But real-world applications need a database to: Store user data. Manage products. Persist application state. In this blog, we’ll connect FastAPI with a database using SQLAlchemy and perform CRUD operations . What is SQLAlchemy? SQLAlchemy is a powerful Python ORM (Object Relational Mapper) that allows you to: Interact with databases using Python code. Avoid writing raw SQL queries. Work with models instead of tables. Step 1: Install Dependencies pip install sqlalchemy (For SQLite, no extra driver needed) Project Structure Update app/ ├── main.py ├── database/ │ ├── connection.py ├── models/ │ ├── item.py ├── schemas/ │ ├── item.py ├── routes/ │ ├── item.py Step 2: Database Connection app/database/connection.py from sqlalchemy import create_engine from sqlalchemy . orm import sessionmaker , declarative_base DATABASE_URL = "sqlite:///./test.db" engine = create_engine ( DAT...
Read more

Middleware & CORS in FastAPI

  Introduction As your API grows, you may need to: Log requests Modify responses Handle cross-origin requests This is where Middleware and CORS (Cross-Origin Resource Sharing) come into play.They help control how requests and responses flow through your application. What is Middleware? Middleware is a function that runs: Before a request reaches your route After a response is returned Think of it as a pipeline layer between client and server. Request Flow with Middleware Client → Middleware → Route → Middleware → Response Creating Custom Middleware from fastapi import FastAPI , Request import time app = FastAPI () @ app . middleware ( "http" ) async def log_requests ( request : Request , call_next ): start_time = time . time() response = await call_next ( request ) process_time = time . time() - start_time print ( f"Request: { request . url} | Time: { process_time } " ) return response What This Middle...
Read more

Python Data Handling

Python Data Handling for Beginners In Python, data handling means storing, changing, and using data in different ways. In this post, you will learn: Lists Tuples Dictionaries Sets String Methods Type Conversion 1. Python Lists :Lists store many values in one variable.                fruits = ["apple", "banana", "mango"]                                                                       Method                          Use                                            append()          ...
Read more