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?

Billboard image

Monitoring as code

With Checkly, you can use Playwright tests and Javascript to monitor end-to-end scenarios in your NextJS, Astro, Remix, or other application.

Get started now!

Top comments (0)

Heroku

This site is powered by Heroku

Heroku was created by developers, for developers. Get started today and find out why Heroku has been the platform of choice for brands like DEV for over a decade.

Sign Up

👋 Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay