DEV Community

Cover image for Advanced Querying with Joins
arjun
arjun

Posted on

3 1 1 2

Advanced Querying with Joins

Day 4: Advanced Querying with Joins

Date: January 22, 2025

Today, we delve into one of the most critical concepts in SQL: Joins. Joins are essential when working with relational databases, allowing us to combine data from multiple tables into a single, cohesive dataset. This skill is indispensable for real-world projects where data is often scattered across multiple related tables.


What Are Joins?

Joins are SQL operations that combine rows from two or more tables based on a related column between them. They enable us to analyze and present data comprehensively by linking the information stored in separate tables.


Types of Joins

1. INNER JOIN

Retrieves records with matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

Example:

Retrieve users who have placed orders:

SELECT users.name, orders.order_id, orders.total 
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

2. LEFT JOIN (or LEFT OUTER JOIN)

Retrieves all records from the left table and the matching records from the right table. If no match exists, NULL values are returned for the right table's columns.

Syntax:

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

Example:

Retrieve all users and their orders, including users with no orders:

SELECT users.name, orders.order_id, orders.total 
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

3. RIGHT JOIN (or RIGHT OUTER JOIN)

Retrieves all records from the right table and the matching records from the left table. If no match exists, NULL values are returned for the left table's columns.

Syntax:

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

Example:

Retrieve all orders and their corresponding user details, including orders with no associated user:

SELECT users.name, orders.order_id, orders.total 
FROM users
RIGHT JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

4. FULL OUTER JOIN

Retrieves all records when there is a match in either table. If no match exists, NULL values are returned for the non-matching table's columns.

Syntax:

SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Enter fullscreen mode Exit fullscreen mode

Example:

Retrieve all users and all orders, whether or not they match:

SELECT users.name, orders.order_id, orders.total 
FROM users
FULL OUTER JOIN orders
ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

Practice: Writing Queries

Scenario: Users and Orders Tables

  • users table:

    | id | name | email |

    |----|-------|--------------------|

    | 1 | Alice | alice@example.com |

    | 2 | Bob | bob@example.com |

    | 3 | Carol | carol@example.com |

  • orders table:

    | order_id | user_id | total |

    |----------|---------|-------|

    | 101 | 1 | 250 |

    | 102 | 2 | 400 |

    | 103 | 4 | 300 |


Practice Queries

  1. Fetch All User Orders Using INNER JOIN:
   SELECT users.name, orders.order_id, orders.total 
   FROM users
   INNER JOIN orders
   ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

Result:

| name | order_id | total |

|-------|----------|-------|

| Alice | 101 | 250 |

| Bob | 102 | 400 |

  1. Fetch All Users and Their Orders Using LEFT JOIN:
   SELECT users.name, orders.order_id, orders.total 
   FROM users
   LEFT JOIN orders
   ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

Result:

| name | order_id | total |

|-------|----------|-------|

| Alice | 101 | 250 |

| Bob | 102 | 400 |

| Carol | NULL | NULL |

  1. Fetch All Orders and Their Corresponding Users Using RIGHT JOIN:
   SELECT users.name, orders.order_id, orders.total 
   FROM users
   RIGHT JOIN orders
   ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

Result:

| name | order_id | total |

|-------|----------|-------|

| Alice | 101 | 250 |

| Bob | 102 | 400 |

| NULL | 103 | 300 |

  1. Fetch All Users and Orders Using FULL OUTER JOIN (Supported in PostgreSQL):
   SELECT users.name, orders.order_id, orders.total 
   FROM users
   FULL OUTER JOIN orders
   ON users.id = orders.user_id;
Enter fullscreen mode Exit fullscreen mode

Result:

| name | order_id | total |

|-------|----------|-------|

| Alice | 101 | 250 |

| Bob | 102 | 400 |

| Carol | NULL | NULL |

| NULL | 103 | 300 |


Real-World Example: Display Orders for a Specific User

Suppose you want to display all orders made by a user named "Alice":

SELECT users.name, orders.order_id, orders.total 
FROM users
INNER JOIN orders
ON users.id = orders.user_id
WHERE users.name = 'Alice';
Enter fullscreen mode Exit fullscreen mode

Result:

| name | order_id | total |

|-------|----------|-------|

| Alice | 101 | 250 |


Interview Preparation

  1. What are Joins in SQL, and why are they used?
  2. Differentiate between INNER JOIN and LEFT JOIN.
  3. How does FULL OUTER JOIN handle non-matching rows?
  4. Write a query to retrieve data from three tables using JOIN.

Outcome for the Day

By the end of Day 4, you should:

  • Understand and use different types of SQL Joins.
  • Write queries that combine data from multiple tables.
  • Apply these concepts to your project to display meaningful insights.

Tomorrow, we’ll focus on Data Normalization and Best Practices, a critical step to design efficient and scalable databases.


Would you like further examples or explanations for Joins?

API Trace View

How I Cut 22.3 Seconds Off an API Call with Sentry 👀

Struggling with slow API calls? Dan Mindru walks through how he used Sentry's new Trace View feature to shave off 22.3 seconds from an API call.

Get a practical walkthrough of how to identify bottlenecks, split tasks into multiple parallel tasks, identify slow AI model calls, and more.

Read more →

Top comments (0)

👋 Kindness is contagious

Immerse yourself in a wealth of knowledge with this piece, supported by the inclusive DEV Community—every developer, no matter where they are in their journey, is invited to contribute to our collective wisdom.

A simple “thank you” goes a long way—express your gratitude below in the comments!

Gathering insights enriches our journey on DEV and fortifies our community ties. Did you find this article valuable? Taking a moment to thank the author can have a significant impact.

Okay