Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Inner join question

Posted on 2011-03-08
5
Medium Priority
?
236 Views
Last Modified: 2012-05-11
I am trying to get my head around the join (or inner/outer) syntax.

If it can be described as

SELECT .......
FROM table1 join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table2.primarykey = table3.foreignkey
where ....

does the second join between table2 and table3 occur because of the text "join table3 ON" comes after table2, or is it because of the condition:  "table2.primarykey = table3.foreignkey"

what is it that defines "what is joined" ?

and could I have the following:

SELECT .......
FROM table1 join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table1.primarykey = table3.foreignkey
where ....

i.e have "table1.primarykey = table3.foreignkey"
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 12

Expert Comment

by:enachemc
ID: 35068843
you can have the second
you join all the left side with the table on the right side
0
 
LVL 15

Accepted Solution

by:
derekkromm earned 2000 total points
ID: 35068896
does the second join between table2 and table3 occur because of the text "join table3 ON" comes after table2, or is it because of the condition:  "table2.primarykey = table3.foreignkey"

both


what is it that defines "what is joined" ?

the "ON" clause, as long as the tables referenced in the ON clause are already part of the query

and could I have the following:

SELECT .......
FROM table1 join table2 ON table1.primarykey = table2.foreignkey
join table3 ON table1.primarykey = table3.foreignkey
where ....

i.e have "table1.primarykey = table3.foreignkey"

absolutely

now, the difference between an inner and outer join is as follows:

inner - only matching records from both sides make it into the final result
left outer - all records from the left table and any matching records from the right table
right outer - all records from the right table and any matching records from the left table

so if you have the following tables/data:

id x
1 1
2 2
3 1
4 5

id y
1 10
3 4
5 100

inner join would result in:

id x y
1 1 10
3 1 4

left outer (1st table left outer 2nd table):

id x y
1 1 10
2 2 null
3 1 4
4 5 null

right outer

id x y
1 1 10
3 1 4
5 null 100

full outer

1 1 10
2 2 null
3 1 4
4 5 null
5 null 100
0
 

Author Comment

by:soozh
ID: 35069306
Thanks for the full description.  

I am still not sure why there must be a table name after the JOIN statement.  Could I write:

select ... from A, B
inner join C on A.AA = B.BB

and what would I get?  It must be the same as:

select ... from A
inner join B on A.AA = B.BB

and if I could write that then why could i not write

select ... from A, B
inner join A.AA = B.BB

0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35069374
the 1st query isn't correct. it would result in the same thing as the 2nd query, except it would then cross join table C (which means every row of A/B would be joined to every row of C. so 100 rows and 100 rows results in 10000 rows)

the 2nd is correctly formatted

the 3rd would actually be

select ... from A, B where A.AA = B.BB

the 2nd/3rd are identical, its just that the 2nd is off the ansi-92 standard and the 3rd is an older standard

you should try to always use the 2nd. as you add more tables, etc, it becomes much more easily readable
0
 

Author Comment

by:soozh
ID: 35069613
ok thanks.

I now realise that :

select ... from A, B
inner join C on A.AA = B.BB

is a cross join on C i understand the syntax.

I dont understand why it has taken me so ling to get my head around it.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question