Solved

Inner join question

Posted on 2011-03-08
5
233 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 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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone 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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

688 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