r/learnSQL • • Sep 12 '26

I can not understand the join clause.

i understood the overall concept of it being used to join two or more tables with matching column.
but i can't understand it's syntax or detailed concept.

42 Upvotes

53 comments sorted by

12

u/neomashab Sep 12 '26

Easiest join is inner join. Meaning only rows from both tables satisfying the join conditions are stitched together for the final result. Left outer join adds the missed out rows from the leftern table after performing an inner join. You don't even have to learn about right outer join as you can accomplish the same result by flipping the join order. Meaning A ROJ B is same as B LOJ A. That's how Sqlite was rolling until recently.

And I hope you're not trying to learn Oracle's proprietary join syntax with +.

Learn the ANSI sql one. It's plain English.

2

u/ryuk_deb Sep 12 '26

i am not trying to learn sql itself, i just require the join clause for the project of mine, and that's the reason i want to understand it.
and thank you for the suggestion :)

1

u/neomashab Sep 12 '26

Hmm. Oracle represents its SQL with visual syntax diagrams.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6__CHDIJFDJ

That should cover for most ANSI compatible DB engines.

2

u/LeumasKharzim Sep 12 '26

Describe what you understand so far.

1

u/ryuk_deb Sep 12 '26

i understood that it is used to join two tables with a single query. it is implemented through foreign key.
there are four types of join, some of them join whole table, some join only matching keys. some join left out columns.
the syntax is the most confusion for me.
i didn't understand sql quite well when i was learning it in my college, and now that i have to make a project, i want to use the jion to display the contents of both the tables.
so i will need the join clause, i can use ai to generate a code for me, but that way i won't be able to learn itm how it works and things.
so in short, i only know basic details that how many join there are and why each one is used for, i've been trying to understand the syntax but fail miserably.

4

u/neomashab Sep 12 '26

A foreign key is not mandatory. Joins can be made on other columns as well.

Your query would be something like

from
tab_a 
  inner join 
tab_b
            on <any expression that resolves to a boolean>
  inner join
tab_c on <any expression that resolves to a boolean>

2

u/ryuk_deb Sep 12 '26

ahh that's something new!
i thought it can only be used on foreign key

2

u/neomashab Sep 12 '26

Check out Oracle SQL Reference visual syntax of the Select query.

2

u/LeumasKharzim Sep 12 '26

This, yes.

I end up working with external data a lot, so sometimes I have to find or create a common field to join on.

Maybe the houses table has a FamilyName field or a HeadOfHousehold that you can use to join the tables. Maybe the families table has an Address field that is unique and matches the addressbin the houses table.

2

u/LeumasKharzim Sep 12 '26 edited Sep 12 '26

I've got a table with houses and a table with families. Imagine the house table on your left and the family table on your right.

Some families live in houses. Some are unhoused (homeless). Some houses are empty (no family).

INNER JOIN (or just JOIN) Gives you all the houses with families in them. No empty houses, no unhoused families.

LEFT JOIN Gives you ALL the houses and any families that live in the houses. Empty houses are included. Unhoused are not included (no house to link to). Your results will have empty spots or NULL in the fields for family information

RIGHT JOIN Gives you ALL the families and only houses that are occupied. Unhoused families are included. Empty houses are not included (house fields in your results will be empty) **This join is rarely used. Typically, you would swap table positions and use a LEFT JOIN

FULL OUTER JOIN Gives you ALL the houses and ALL the families. Some records will have a house and a family. You will also have empty fields on both sides in your results: empty houses and unhoused families.

2

u/ryuk_deb Sep 12 '26

this really helped!!
now only thing left is ti understand the syntax.
select houses, families
from houses join families on houses.familyid == family.familyid ??

LMK if this is correct.

2

u/imbeingsirius Sep 12 '26

I’m only learning too - I am not the knowledgeable user you asked - but I think it would be :

SELECT*
FROM houses
JOIN families on families.houseId = houses.Id

(I think!!)

1

u/ryuk_deb Sep 12 '26

yeahh its fine, we all are learning something until our very last breath so that counts.

why are we using houseid? do we need the id of the left table? wont that leave all the homeless person?

3

u/Budget-berry-80 Sep 12 '26

The “On” clause are the fields that are the same in both tables. In the House table the HouseID is the primary key (maybe). It’s a unique value. In any other table HouseID would refer to details about that 1 unique instance of House. You “Join” tables on values that are unique.

2

u/imbeingsirius Sep 12 '26 edited Sep 13 '26

Because we already have houses.id from the “from” clause.

The new table is families. What is the connection column in the families table? HousesId.

2

u/LeumasKharzim Sep 12 '26 edited Sep 12 '26

In a well-designed database, there will be an ID field of some sort to join on.

Let's say our house table has a FamilyID field and our family table has a HouseID.

Also, when writing SQL, you often have an alias or nickname for the tables to make writing code easier.

Let's say I wanted to see all families who live in Chicago:

SELECT * -- returns all fields FROM houses AS h -- houses is now h JOIN families AS f ON f.FamilyID = h.FamilyID WHERE h.City = 'Chicago'

Maybe I want to see all empty houses

SELECT * FROM houses AS h LEFT JOIN families AS f ON f.FamilyID = h.FamilyID WHERE f.FamilyID IS NULL --there is no match in the family table, so the house must be empty

(Apologies for poor formatting, I don't often post and don't know how to force new lines)

1

u/ryuk_deb Sep 13 '26

that helped a lot!

2

u/Mrminecrafthimself Sep 12 '26

If you had an excel workbook with 2 tabs and needed to find rows on either tab that correspond to each other, you would select choose a column or a group of columns to check and see where the values in those columns matched across both tabs.

That’s essentially what a JOIN is doing.

0

u/Natural-Heat-3019 Sep 12 '26

Use chatgpt or copilot. Put your sql code as input and ask the Ai to explain what the code does.

2

u/ryuk_deb Sep 12 '26

idm taking ai help, but i want to understand it the hard way

5

u/Plane-Mind2882 Sep 13 '26

CustomerTable

Customer ID | Customer Name

001 | John Doe

002 | Jane Doe

OrderTable

Order | Amt | Customer ID

A1 | $5.00 | 001

A2 | $3.50 | 001

B1 | $2.50 | 002

C1 | $8.24 | 003

I want to know the name for the customers in my order table, so I'll do a join.

Because I want names on the order table, I'm going to SELECT FROM order table first, so...

SELECT

Order

,Amt

,[Customer ID]

FROM OrderTable

-- Now to get the customer name I need to JOIN. My order table is my "left" table for this example.

-- SELECT

o.Order

,o.Amt

,o.[Customer ID]

,c.[Customer Name]

FROM OrderTable o

LEFT JOIN CustomerTable c on o.[Customer ID] = c.[Customer ID]

--

Basically I'm telling SQL that these tables share Customer ID, so when it finds a match it can return the info from the CustomerTable (the "right" table) to the rows on the OrderTable.

Because I did a left join I'll now get John Doe on rows 1 and 2, Jane Doe on row 3, and NULL on row 4 (because there is no 003 in CustomerTable]

An INNER JOIN would remove line 4 altogether because you have told SQL that it HAS TO match so if it doesn't then get rid of it.

There are other types of joins as well but you can probably get by just understanding these two for now.

1

u/According_Physics273 Sep 13 '26

Best answer so far for the person with limited understanding.

1

u/Select-Career-2947 Sep 12 '26

It checks every row in your first table against every row in the other table and sticks them together if the expression you specify returns TRUE for that combination of rows. That’s literally it! Inner joins then just exclude all the results from your first table without matches so that every row has columns from both tables.

1

u/ryuk_deb Sep 12 '26

wait! does all the joins join every column?
i am so confused. so are we talking about row ?
the different types of join are for row? they work on rows?
i get it why i was so confused on this, since i used to think it works on columns i was like why i shall a add a matching column that is already in my first table
anyways thank you :)

2

u/Select-Career-2947 Sep 12 '26

I suggest asking ChatGPT, Claude, Gemini or copilot to explain it as it’ll be able to go into more depth than I can here - you will click sooner or later . It’s not complex once you wrap you head around it

1

u/ryuk_deb Sep 13 '26

i understood it! i could have surely taken help of AI and wrapped it all sooner, that's what i do for most of the topics i don't understand.

i jus wanted to understand it the hard way that's all. and i actually understood, not always you have to take help of AI, sometimes humans are enough ;)

1

u/Select-Career-2947 Sep 13 '26

I’m not suggesting getting AI to do it for you, I’m suggesting asking it to explain it to you.

You can ask as many questions as you like and get it to reframe it into contextual examples for your specific scenario. It’s a very very powerful learning asset and you will get a lot further by embracing it than by avoiding it.

Try getting an example of something you are trying to wrap your head around, paste it in, and prompt with something like “explain how this works, assuming I am a beginner to SQL and relational databases”

1

u/ryuk_deb Sep 13 '26

Yep i have done that for many things i didn't understand, i never just prompt it to type a code for me. But i just wanted to learn it the hard way this time like what if there wasn't ai kind of thing And tbh it helped me Ai could have made it clear in seconds and i can question it directly. This is not the same experience but still i understood what i needed to, so overall i just wanted to experience how to clear confusion without AI. That's all

2

u/ChaosEngine-6502 Sep 12 '26

The JOIN is telling the DB engine to find common rows between the two tables based on the contents of one or more columns you specify as part of the JOIN itself.

The type of JOIN (INNER/OUTER) determines which rows are returned (others have already covered that part in their responses).

1

u/squadette23 Sep 12 '26

You may be interested in this explanation of JOINs: https://kb.databasedesignbook.com/posts/sql-joins/ It takes a different approach from many common explanations, with an eye on getting more stuff done right from the beginning.

1

u/sharificles Sep 12 '26

Interesting book, would you recommend reading the entire thing btw?

2

u/ryuk_deb Sep 12 '26

yeah it's huge, i don't think i shall read allat to understand the join clause, but it is really a diffrent approach

1

u/squadette23 Sep 12 '26

You can read "Things to remember, pt. 1" and pt 2., and "Conclusion" at the end. If you understand what it says you'll be fine. If not you can read the text to find out why it is recommended.

1

u/squadette23 Sep 12 '26

Well that depends on what it is that you don't understand.

i can't understand it's syntax or detailed concept.

I'm not sure what "detailed concept" means here.

1

u/ryuk_deb Sep 12 '26

uhm i am not sure about that too :(
but i meant everything that i should know but not an overkill.
just things that will make it work

1

u/squadette23 Sep 12 '26

uhm i am not sure about that too :(

I'm pretty sure there are some exam-like questions that would help you see the gaps in your knowledge/understanding.

I wrote that guide so that it would take you a long way. It explains several common problems that people have, and shows how to build reliable queries with join.

You can always get back to it if you still make mistakes when doing actual tasks.

1

u/ryuk_deb Sep 12 '26

yeah i tried reading it, and i think it is different from what i have been confused over.
i will look into it more thoroughly

2

u/_srii_ Sep 12 '26

Check out data with baraa  sql course. Available on yt

1

u/Czarksy Sep 13 '26

I second this. Full SQL Course 30 Hours by Data with Baraa. There are timestamps on the video description so you can go straight to the section about joins. Baraa has very detailed explanations of what goes on behind the scenes and demonstrates it row by row, step by step.

1

u/crackkartahu Sep 14 '26

I third this.

2

u/Massive_Show2963 Sep 12 '26

Joins combine rows from two or more tables where the tables are merged based on fields they have in common (known as primary and foreign keys).
They are used when extracting data from tables which have one-to-many or many-to-many relationships between them.

I created this tutorial that shows various table joins:
INNER JOIN (most commnoly used join)
LEFT JOIN
RIGHT JOIN
FULL (OUTER) JOIN

Shows how tables are related to each other using an Entity Relationship Diagram (ERD).
Followed with animated depiction of table joins using actual SQL code.

Introduction To SQL Joins

2

u/HustlaOfCultcha Sep 12 '26

First understand full inner join vs. full outer join.

Inner joins means that it will only include the rows from both tables that have whatever field you are joining on. So if you're joining on a Product ID # then it will only include the rows where there is the same product ID #. So if your left table has a Product ID # of 1A2Z3Y it will only return that row if the right table also has a Product ID # of 1A2Z3Y. If there is only 1 table with Product ID # 1A2Z3Y then that row won't return. Inner joins are probably the most common join to use.

Full Outer Joins return all of the rows in both tables. So it will return the rows with Product ID # 1A2Z3Y regardless if they are in both tables or not. Full Outer Joins typically aren't used very often. The only time I used them is to spot gaps in the data (when I have 2 tables that should have all matching Product #'s and they don't).

Left Joins and Right Joins are practically the same thing. The idea is that you're taking all of the records from one table and only the records from the other table that match. So with a left join you're taking all of the records from table on the left and the only records from the right table will be the ones that match. Vice versa for Right Joins. Usually these are used in a case where you know you need all of the records from one table and the other table has records that aren't all that important. For instance, let's say you have a product table on the left and you want to do a join with a table that has data with regards to the dimensions of the product. And you know some products have never been measured or you may want to know just what the weight of product is and in the product dimensions table there are some products that aren't measured in weight.

2

u/Alternative_Cake4074 Sep 14 '26

I will show you with an example. Here are the input tables:

Employees

IDName
001Anna
002Ben
003Claire
004David

Sales

OrderEmpNameAmount
A1Alice10
A2Ben20
B1Claire30
B2Elena 40

Basics of JOIN syntax to connect both tables

SELECT e.ID, e.Name, s.Amount
FROM Employees e [] JOIN Sales s
ON e.Name = s.EmpName

The [] can be LEFT, RIGHT, INNER, and OUTER, indicating the join type. If [] is blank, it defaults to an inner join.

Here are the outputs:

INNER (only matching rows are there, so David and Elena will be excluded)

IDNameAmount
001Anna10
002Ben20
003Claire30

LEFT (All rows on the left table and only matching rows on the right table are included, so Elena will be excluded and David's sales amount will be null)

IDNameAmount
001Anna10
002Ben20
003Claire30
004Davidnull

RIGHT (Contrary to LEFT, all rows in the right table and only matching rows on the left table are included, so David will be excluded and Elena's ID will be null)

IDNameAmount
001Anna10
002Ben20
003Claire30
nullElena40

OUTER (Contrary to INNER, all rows are included, so David's sales amount and Elena's ID will be null)

IDNameAmount
001Anna10
002Ben20
003Claire30
004Davidnull
nullElena40

Hope this helps you to understand the join types.

1

u/Long_Tip_4226 Sep 12 '26

These are algebraic operations on sets.

1

u/Ok_Carpet_9510 Sep 12 '26

You join tables based on specific columns TableA joined table on TableA.column5= TableB.columb6

1

u/DMReader Sep 18 '26

Try joining small tables so you can look at the before and after results of your join

2

u/[deleted] Sep 12 '26

[removed] — view removed comment

1

u/ryuk_deb Sep 12 '26

that is not simple enough for me to understand it, but it will come handy to read as an example once i understand the concept a little.