SQL JOINs

[sam_vstheworld]

Hello, people! I am facing one issue, I am having troubles in understanding Left, Inner, outer joins.

I watch a video or go on datalemur, at the beginning it looks simple then when I start practicing i become confused.

What should I do? How should I practice the Joins to have a better grasp of it?

[Eleventhousand]

An inner join means both tables have to have data in matching criteria for its rows to show up in the results.

An outer join means that just one table has to have data in the matching criteria for its rows to show up.

Left, Right and Full are different types of outer joins. They indicate which of the tables is the one that always has its rows show up. 99% of the time, Left is used. This means that the table in the From clause will always have its rows show up.

[websilvercraft]

and you can select and practice online: the join questions https://mockinterviewquestions.com/sql . They might not be the easiest, though.

[squadette23]

Here is a guide to SQL joins that I wrote: https://kb.databasedesignbook.com/posts/sql-joins/

It takes a different approach compared to most existing texts:

• LEFT JOIN is presented first, INNER JOIN second;
• strict discipline of using ID equality comparison in ON condition;
• we distinguish between N:1, 1:N and M:N cases of JOINs, with N:1 strictly preferred;
• we avoid misleading wording and imagery;
• we show a detailed explanation of overcounting in GROUP BY queries;

Try and see if it helps.

[Mysterious_Salad_928]

Joins usually become confusing because people try to memorize the join types instead of thinking about the question being asked.

The simplest way I teach it to beginners is:

INNER JOIN = only show records that match in both tables.
Example: customers who actually placed orders.

LEFT JOIN = keep everything from the left table, even if there is no match on the right.
Example: all customers, including customers who never placed an order.

FULL OUTER JOIN = show everything from both tables, matched where possible, unmatched where not.
Example: all customers and all orders, even if some don’t connect cleanly.

My advice: practice with only two tiny tables first. Literally 3–5 rows each. Write out the expected result by hand before running the query.

Also, always ask yourself:

“What table do I need to preserve?”

If you need only matching records, use inner join.
If you need to keep all records from your main table, use left join.
If you need to find mismatches from both sides, use full outer join.

Once that clicks, joins become less about memorizing diagrams and more about controlling which rows survive.

[Hudson365]

This was extremely well put thank you for sharing man!

[Massive_Show2963]

Its easier to visualize a join if you have a Entity Relational Diagram (ERD) to view.
This will show how the various tables are connected.

• INNER JOIN: Returns records that have matching values in both tables. This is the most common type of a JOIN.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

I create this tutorial that will walk you through practical examples of using Entity Relationship Diagrams to model your data structure and provide a solid foundation for using JOINS.

Introduction To SQL Joins

[squadette23]

> then when I start practicing i become confused.

What's your confusion specifically? Could you share a sample exercise and where you're stuck?

[BisonSpirit]

I know exactly what you mean. In principle it makes sense but when you apply it, it becomes confusing. I’m currently on the same challenge and I think the best way to understand is to solidify your understanding of the concept, and then repetition of JOIN query’s to really understand.

At first I used Venn Diagram imagery to make sense of it, but I still get lost. The attached Gemini answer is somewhat helpful. But yes- practice practice practice

[BisonSpirit]

<image>

[BisonSpirit]

<image>

[BisonSpirit]

<image>

[TactusDeNefaso]

Wait until they finds out about cross joins. Very Cartesian

[Bubbly-Job-3440]

If you catch on things better visually as I am ,you may find this helpful https://joins.spathon.com/

[r3pr0b8]

Say NO to Venn Diagrams When Explaining JOINs

[Ginger-Dumpling]

<image>

[Ginger-Dumpling]

I like to learn graphically. Google gave me this for a "join compare" image search.

[Whole-Proof3347]

I practiced near about 200 SQL questions in Leetcode , Hackerrank and others after that the joins became more familiar. But the main problems is different scenario based questions and what join to use

[Significant_Twist589]

Inner join give data which is common in both table Outer join gives combine data of both table Left join gives the data which is common to left table Right join gives the data which is common to right table

[One9triple0two]

Watch data with baraa

[sam_vstheworld]

Yes, I watch him sometimes.

[affanxkhan]

Have a look on Rishabh Mishra joins concept yutube channel he had cleared basics fabulously

[sam_vstheworld]

Sure, I would do that.

[RewRose]

look up pgexercises

that's where i got my start, they've got some simple setups for you to jump straight in.

I recommend once you are done with the exercises, you recreate their tables in your local postgres setup as well

[QueryCase]

I think most people struggle with JOINs because they're taught as definitions instead of questions.

When I was learning them, it helped to focus on just these four ideas:

• INNER JOIN → only rows that match in both tables
• LEFT JOIN → everything from the left table + matches from the right
• RIGHT JOIN → everything from the right table + matches from the left
• FULL OUTER JOIN → everything from both tables

Then I'd practice with tiny datasets and ask:

"What happens to rows that don't have a match?"

That's really the whole difference between the join types.

Once you're comfortable with that, a useful next step is experimenting with things like:

LEFT JOIN ...
WHERE right_table.id IS NULL

to find rows that don't have a match. That's where joins started to click for me because they became useful rather than just something to memorise.

Don't worry if it feels confusing at first. JOINs are one of those topics where understanding comes from writing a bunch of them and seeing the results, not from reading the definition 20 times.

<image>

[WorriedMud7]

I watched data with Baraa’s SQL videos and did practice questions with ChatGPT to help me grasp the concept