Solved

Inner join question

Posted on 2011-03-08
5
227 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
  • 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 500 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now