Solved

Inner join question

Posted on 2011-03-08
5
231 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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.…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

789 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