?
Solved

Inner join question

Posted on 2011-03-08
5
Medium Priority
?
234 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

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month10 days, 6 hours left to enroll

765 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