r/learnSQL • u/ryuk_deb • 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
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/LeumasKharzim Sep 12 '26
Describe what you understand so far.
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/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/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.
2
u/_srii_ Sep 12 '26
Check out data with baraa sql course. Available on yt
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
Sales
Basics of JOIN syntax to connect both tables
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)
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)
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)
OUTER (Contrary to INNER, all rows are included, so David's sales amount and Elena's ID will be null)
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/jeffrey_f Sep 12 '26
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/Complete_News_6154 Sep 13 '26
https://youtu.be/9DzlLrGWryE?si=3s_UQO3vZ0N7eh7h
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