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?

Postgres on Neon - Get the Free Plan

No credit card required. The database you love, on a serverless platform designed to help you build faster.

Get Postgres on Neon

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay