September 30, 2008 at 7:53 am
Can U explain in detail when we are joinning 4 tables what will happen?
1. Joining type - Inner Join
2. Joining type - Left Outer Join
3. Joining type - Right Outer Join
4. Joining type - Full Outer Join
September 30, 2008 at 8:16 am
rana_subhankar (9/30/2008)
Can U explain in detail when we are joinning 4 tables what will happen?1. Joining type - Inner Join
2. Joining type - Left Outer Join
3. Joining type - Right Outer Join
4. Joining type - Full Outer Join
That will depend on placement of you ON clause. And who is joined to who in each kind of join. As well do you mean what happens in each of the 4 join types if only that type is used for the joining of the 4 tables or a combination of join types from them?
October 1, 2008 at 7:34 am
I do this all the time but my theory is a little weak. This is an example with TWO tables.
SET NOCOUNT ON;
CREATE TABLE Table1(
ID int IDENTITY,
Description varchar(32),
ItemID int);
INSERT Table1 (Description, ItemID) Values ('Table1-Row1', 2)
INSERT Table1 (Description, ItemID) Values ('Table1-Row2', 4)
INSERT Table1 (Description, ItemID) Values ('Table1-Row3', null)
INSERT Table1 (Description, ItemID) Values ('Table1-Row4', 6)
SELECT * FROM Table1
CREATE TABLE Item(
ItemID int IDENTITY,
Description varchar(32));
INSERT Item (Description) Values ('Item1')
INSERT Item (Description) Values ('Item2')
INSERT Item (Description) Values ('Item3')
INSERT Item (Description) Values ('Item4')
SELECT * FROM Item
PRINT 'EquiJoin - The ON clause is an equality between matching columns??'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
Select *
FROM Table1 INNER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'LEFT OUTER JOIN - All rows from left-hand table (Table1), only matching rows from right-hand table (Item)'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
SELECT *
FROM Table1 LEFT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'RIGHT OUTER JOIN - All rows from right-hand table (Item), only matching rows from right-hand table (Table1)'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
SELECT *
FROM Table1 RIGHT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'CROSS JOIN - all combinations, no ON clause limiting the combinations??'
SELECT *
FROM Table1 CROSS JOIN Item
DROP TABLE Table1
DROP TABLE Item
results:
ID Description ItemID
----------- -------------------------------- -----------
1 Table1-Row1 2
2 Table1-Row2 4
3 Table1-Row3 NULL
4 Table1-Row4 6
ItemID Description
----------- --------------------------------
1 Item1
2 Item2
3 Item3
4 Item4
EquiJoin - The ON clause is an equality between matching columns??
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
LEFT OUTER JOIN - All rows from left-hand table (Table1), only matching rows from right-hand table (Item)
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL NULL NULL
4 Table1-Row4 6 NULL NULL
RIGHT OUTER JOIN - All rows from right-hand table (Item), only matching rows from right-hand table (Table1)
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
NULL NULL NULL 1 Item1
1 Table1-Row1 2 2 Item2
NULL NULL NULL 3 Item3
2 Table1-Row2 4 4 Item4
CROSS JOIN - all combinations, no ON clause limiting the combinations??
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 1 Item1
2 Table1-Row2 4 1 Item1
3 Table1-Row3 NULL 1 Item1
4 Table1-Row4 6 1 Item1
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 2 Item2
3 Table1-Row3 NULL 2 Item2
4 Table1-Row4 6 2 Item2
1 Table1-Row1 2 3 Item3
2 Table1-Row2 4 3 Item3
3 Table1-Row3 NULL 3 Item3
4 Table1-Row4 6 3 Item3
1 Table1-Row1 2 4 Item4
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL 4 Item4
4 Table1-Row4 6 4 Item4
Is the CROSS JOIN the same as a "full outer join"?
The "theta-joins" aka "triangle joins" are what you get if you have anything other than an equality in the ON clause, right?
Jeff Moden posted a great explanation of those but I can't seem to find the silly thing right now.
October 1, 2008 at 7:53 am
http://www.sqlservercentral.com/articles/T-SQL/61539/
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."
October 1, 2008 at 12:19 pm
http://www.sqlservercentral.com/articles/Basic+Querying/ansijoins/189/
nice (if old) article on types of joins.
BTW CROSS JOIN is not the same as FULL OUTER JOIN.
SET NOCOUNT ON;
CREATE TABLE Table1(
ID int IDENTITY,
Description varchar(32),
ItemID int);
INSERT Table1 (Description, ItemID) Values ('Table1-Row1', 2)
INSERT Table1 (Description, ItemID) Values ('Table1-Row2', 4)
INSERT Table1 (Description, ItemID) Values ('Table1-Row3', null)
INSERT Table1 (Description, ItemID) Values ('Table1-Row4', 6)
SELECT * FROM Table1
CREATE TABLE Item(
ItemID int IDENTITY,
Description varchar(32));
INSERT Item (Description) Values ('Item1')
INSERT Item (Description) Values ('Item2')
INSERT Item (Description) Values ('Item3')
INSERT Item (Description) Values ('Item4')
SELECT * FROM Item
PRINT 'EquiJoin - The ON clause is an equality between matching columns'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
Select *
FROM Table1 INNER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'LEFT OUTER JOIN - All rows from left-hand table (Table1), only matching rows from right-hand table (Item)'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
SELECT *
FROM Table1 LEFT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'RIGHT OUTER JOIN - All rows from right-hand table (Item), only matching rows from right-hand table (Table1)'
PRINT ' " ON Table1.ItemID = Item.ItemID" '
SELECT *
FROM Table1 RIGHT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
PRINT 'CROSS JOIN - all combinations, no ON clause limiting the combinations'
SELECT *
FROM Table1 CROSS JOIN Item
PRINT 'FULL OUTER JOIN - all matching rows plus all non-matching rows.'
SELECT *
FROM Table1 FULL OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
ORDER BY Table1.Description, Item.Description
PRINT 'Simulated FULL OUTER JOIN using union of a Left and Right outer join'
SELECT *
FROM Table1 LEFT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
UNION
SELECT *
FROM Table1 RIGHT OUTER JOIN Item
ON Table1.ItemID = Item.ItemID
ORDER BY Table1.Description, Item.Description
DROP TABLE Table1
DROP TABLE Item
results:
ID Description ItemID
----------- -------------------------------- -----------
1 Table1-Row1 2
2 Table1-Row2 4
3 Table1-Row3 NULL
4 Table1-Row4 6
ItemID Description
----------- --------------------------------
1 Item1
2 Item2
3 Item3
4 Item4
EquiJoin - The ON clause is an equality between matching columns
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
LEFT OUTER JOIN - All rows from left-hand table (Table1), only matching rows from right-hand table (Item)
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL NULL NULL
4 Table1-Row4 6 NULL NULL
RIGHT OUTER JOIN - All rows from right-hand table (Item), only matching rows from right-hand table (Table1)
" ON Table1.ItemID = Item.ItemID"
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
NULL NULL NULL 1 Item1
1 Table1-Row1 2 2 Item2
NULL NULL NULL 3 Item3
2 Table1-Row2 4 4 Item4
CROSS JOIN - all combinations, no ON clause limiting the combinations
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
1 Table1-Row1 2 1 Item1
2 Table1-Row2 4 1 Item1
3 Table1-Row3 NULL 1 Item1
4 Table1-Row4 6 1 Item1
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 2 Item2
3 Table1-Row3 NULL 2 Item2
4 Table1-Row4 6 2 Item2
1 Table1-Row1 2 3 Item3
2 Table1-Row2 4 3 Item3
3 Table1-Row3 NULL 3 Item3
4 Table1-Row4 6 3 Item3
1 Table1-Row1 2 4 Item4
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL 4 Item4
4 Table1-Row4 6 4 Item4
FULL OUTER JOIN - all matching rows plus all non-matching rows.
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
NULL NULL NULL 1 Item1
NULL NULL NULL 3 Item3
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL NULL NULL
4 Table1-Row4 6 NULL NULL
Simulated FULL OUTER JOIN using union of a Left and Right outer join
ID Description ItemID ItemID Description
----------- -------------------------------- ----------- ----------- --------------------------------
NULL NULL NULL 1 Item1
NULL NULL NULL 3 Item3
1 Table1-Row1 2 2 Item2
2 Table1-Row2 4 4 Item4
3 Table1-Row3 NULL NULL NULL
4 Table1-Row4 6 NULL NULL
October 23, 2009 at 12:54 am
However, if you have to sit down and teach (basic) SQL to non-techies (in my case, pensions administrators) it could prove quite useful...;-)
October 23, 2009 at 1:44 am
Yes, it is arrogance. :sick:
It's screaming "I am American/English/whatever and you're not!", "I speak perfect english and you don't", "I'm an arrogant bastard and you kiss my ...".
Arrogance at it's worst!
Lord Of SQL (7/8/2008)
I disagree with the entirety of your response. It is not arrogance to point out ...
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
October 23, 2009 at 2:08 am
Ok guys, so the spelling etc could have been better but as someone whose first language is English English, I despair at some of the spelling I see from people whose first language is American English - a term which I regard as an oxymoron incidentally:-)
I agree that this article addresses pretty basic stuff but as some previous contributors have said, its amazing how many people write SQL statement without actually understanding how (conceptually) the statement is evaluated. The REALLY important thing to understand is the CONCEPTUAL order of evaluation:
1. <from clause>
2. <where clause>
3. <select list>
If the most complex thing a person writes is SELECT * FROM T this understanding isn't necessary but as soon as things get complex (nested <subquery>s, <table expression>s, nested <join table>s etc.) it is vital. What this article did which many do not is to show how the <where clause> or the <on clause> EFFECTIVELY operates on a Cartesian product.
Now in case anyone misunderstands, I put EFFECTIVELY in capitals because for any query optimiser [English English spelling] (regardless of what product), actually manifesting a Cartesian product in the execution of a statement is the act of last desperation (consider joining 3 tables each of a million rows). Query optimisers are free to process a statement in any way they please - provided the result conforms to the standard.
Previous posts also questioned why use the <join table> syntax - and as previously stated, by disambiguating <join condition>s from <search condition>s in the <where clause> add clarity to complex statements - a very clear benefit for anyone who ever has to read or debug code written by others.
As to why the SQL standard has an <inner join> - well its just a case with the general join syntax. The real motivation for the join clause within the SQL-92 standard was to add <outer join>. Again, no doubt experts in SQL are fully familiar with the various flavours [English English spelling] of <outer join> but in my experience:
a. many people are ignorant of when they should user an <outer join> rather than an <inner join>.
a. If they were aware they needed an outer join they would not have the skill to write one correctly in pre '92 sql.
So (almost) in conclusion, I would support the author in providing similar articles covering the other flavours of join, and then go on to cover <grouped table>s. I'm sure that there will be people who are not expert in SQL who will actually learn from seeing basic stuff written down.
And really finally, the SQL-92 standard mentioned is actually an ISO (International) standard (ISO/IEC-9075 for those who care); ANSI (American), like BSI (British), DIN (German), JIS (Japanese) SQL standards are taken from the ISO text with the relevant national foreword and appropriate cover. Constant references to ANSI SQL, like peculiar American-English spelling, irritates non Americans - but we don't make a big thing about it 🙂
October 23, 2009 at 2:13 am
1. Joining type - Inner Join
> intersection of two tables
2. Joining type - Left Outer Join (aka Left Join)
> all from left (first) table and only those from right (second) table that respond to the condition
3. Joining type - Right Outer Join (aka Right Join)
> all from right (second) table and only those from left (first) table that respond to the condition
(I don't find the use for that one, you can always use Left Outer Join instead)
4. Joining type - Full Outer Join (aka Full Join)
> all records from both tables
rana_subhankar (9/30/2008)
Can U explain in detail when we are joinning 4 tables what will happen?1. Joining type - Inner Join
2. Joining type - Left Outer Join
3. Joining type - Right Outer Join
4. Joining type - Full Outer Join
----------------------------------------
I miss SQL Server so much. Now I'm working on Oracle and hate it
October 23, 2009 at 3:09 am
Good info.
Thank you.
October 23, 2009 at 3:20 am
Well, I'm not sure what link a lot of posters clicked on to get here, but the one I clicked on was labeled 'Join the discussion' and definitely not labeled 'Have a rant', 'Add Criticism' or even 'Comment'.
That and the grammar/coding issues aside, I really liked the article.
Not only did the article do a very good job of explaining what to expect and why from the results of an inner join, it also provided hints to some fairly 'advanced' topics.
Using different operators in the joins can be very useful depending on what is required.
One example of where the <= operator could be used, would be to add a cumulative figure to the table or output.
Opening your mind to more than 'equal' and 'not equal' operators on an inner join gives you a much better chance of solving that tricky problem.
Thanks for the article Susantha, and thanks SSC for supporting new writers.
"In faith there is enough light for those who want to believe, and enough shadows for those that don't"
October 23, 2009 at 4:26 am
Very Good piece of writing
October 23, 2009 at 4:28 am
While this was a good beginner article and I liked the visual references, there is a serious danger in showing this to beginners - Joins with > and < in them are a well known way to bring a SQL Server to its knees.
(The Mighty) Jeff Moden wrote this Article[/url] about the RBAR server death that is a triangular join
As for the Grammar Nazi's - I didn't find the typos too distracting.
October 23, 2009 at 4:53 am
SuperDBA-207096 (7/8/2008)
Good article, but it would have been good to proofread it for grammer and typos...
Grammar
😀
October 23, 2009 at 5:04 am
Typos aside I also liked the article 😀
And I would be interested in a follow-up discussing INNER JOIN vs OUTER JOIN vs LEFT JOIN vs RIGHT JOIN, with a similar simple example of two smallish tables.
I remember these INNER vs OUTER joins catching me out inthe past!
Viewing 15 posts - 91 through 105 (of 134 total)
You must be logged in to reply to this topic. Login to reply