SQL Joins Explained: INNER, LEFT, RIGHT, and FULL, With Examples

Introduction

Most real data lives in more than one table. Users live in one table, orders in another, and the link between them is usually an ID, like user_id. A join combines rows from two tables based on that link, so you can ask questions like "show me each order, with the name of the person who placed it."

There are four joins you need first. They differ only in which rows they keep when there is no match.

The example tables

-- users
-- id | name
--  1 | Ali
--  2 | Sara
--  3 | Omar
-- orders
-- id | user_id | item
--  1 |       1 | Keyboard
--  2 |       1 | Mouse
--  3 |       2 | Monitor

Ali has two orders, Sara has one, Omar has none, and every order belongs to a user in this example.

INNER JOIN — only matching rows

An INNER JOIN keeps only rows that match in both tables. Omar disappears here, because he has no orders.

SELECT users.name, orders.item
FROM users
INNER JOIN orders ON users.id = orders.user_id;

Result:

name | item
Ali  | Keyboard
Ali  | Mouse
Sara | Monitor

Use INNER JOIN when you only care about rows that exist on both sides.

LEFT JOIN — all rows from the left table

A LEFT JOIN keeps every row from the first table (the left one), even if there is no match. Unmatched rows get NULL for the other table's columns.

SELECT users.name, orders.item
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

Result:

name | item
Ali  | Keyboard
Ali  | Mouse
Sara | Monitor
Omar | NULL

Use LEFT JOIN when you want "every user, and their orders if they have any" — for example, a report that must include users with zero orders.

RIGHT JOIN — all rows from the right table

A RIGHT JOIN is the mirror image: it keeps every row from the second table (the right one), even if there is no match in the first.

SELECT users.name, orders.item
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

In this example, the result looks like the INNER JOIN result, because every order has a matching user. RIGHT JOIN matters when the right table has rows with no match on the left — for example, orders placed by a deleted user account.

In practice, many developers rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order, because it reads more naturally left to right. Both are correct.

FULL JOIN — all rows from both tables

A FULL JOIN keeps every row from both tables, filling NULL where there is no match on either side.

SELECT users.name, orders.item
FROM users
FULL JOIN orders ON users.id = orders.user_id;

Result:

name | item
Ali  | Keyboard
Ali  | Mouse
Sara | Monitor
Omar | NULL

If there were an order with no matching user, that row would also appear, with NULL for the name. Use FULL JOIN when you need a complete picture from both sides, including unmatched rows on either side.

Choosing the right join

  • Only matching rows: INNER JOIN
  • All left rows, matches if any: LEFT JOIN
  • All right rows, matches if any: RIGHT JOIN
  • All rows from both sides: FULL JOIN

The most common mistake is using INNER JOIN by default and silently losing rows that have no match. If your result looks smaller than expected, check whether a LEFT JOIN was what you actually meant.

Takeaway

A join is just a rule for which rows to keep. Start with INNER for matches only, reach for LEFT when the left table must stay complete, and use FULL when both sides must stay complete. With those four, you can answer most reporting questions in SQL.

Comments

Post a Comment

Popular posts from this blog

Build Your First REST API With Node.js and Express in 30 Minutes

Introduction A REST API is a way for a frontend, a mobile app, or another service to ask your server for data, and to send data back. Node.js with Express is a good first choice for learning APIs because the setup is small and the concepts transfer to other backend frameworks. In this post, you will build a tiny users API with two routes: one to list users, and one to add a user. Step 1: Set up the project Create a folder, then run: npm init -y npm install express Add this to your package.json so you can run the server easily: { "scripts": { "start": "node index.js" } } Step 2: Create the server Create a file called index.js : const express = require("express"); const app = express(); app.use(express.json()); let users = [ { id: 1, name: "Ali" }, { id: 2, name: "Sara" }, ]; // GET /users — list all users app.get("/users", (req, res) => { res.json(users); }); // GET /users/:id — get one...
Read more

Git Branching Explained: Work Without Breaking the Main Code

Introduction A branch is a separate line of work in Git. The main branch, usually called main , holds the stable code. When you start a feature or a fix, you create a branch, work there, and merge it back only when it is ready. If the work goes wrong, the main code is untouched. This is the workflow used by almost every team, and it is worth learning early, before you need it under pressure. Create and switch branches Check your current branch first, then create and switch in one step with the modern switch command, or the older checkout command if your Git version prefers it. git status git switch -c feature/login-form # Older Git: git checkout -b feature/login-form Name branches clearly: feature/login-form , fix/header-bug , docs/readme-update . A good branch name tells the next person what the branch is for, without opening it. Work normally on the branch, then commit as usual: git add . git commit -m "Add login form layout" Merge back to main When the work is ...
Read more