A Refresher on Joins
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/kjambu/2937.asp
-
What you wanted to show is a so-called exception join. But this is not a good way:
select * from t1
where t1.f1 not in (select t2.f1 from t2)
Exception joins should better be implemented like this:
select * from t1
where not exists (select 1 from t2 where t1.f1 = t2.f1)
cheers
jens
-
Better way is:
SELECT *
FROM t1 a
LEFT JOIN t2 b ON a.f1=b.f1
WHERE b.f1 IS NULL
-
Good article, little bit simple and short but its a refresher anyway
-
Rather simple article. It covers the basics, but not much more than that. I think I would have made some concrete tables from the start, to make the examples a bit more readable.
Would also have been interesting to have multiple table joins and then discuss performance issues that come with that. That would have made things interesting for more than the very basic T-SQL coder.
-
Good, quick, basic.
Just what you need for newbies
Can I use it for a course I am teaching?
-
"I am not sure of a real business need for a Cartesian product"
I have seen a pretty useful implementation of a Cartesian product.
Have you ever had a report where you need a series of data grouped by a specific criteria in the series in the data (foreign keys), then by Days, weeks, or months. But when ever there was no data for the day week or month it would simply be missing from your report? Well that is fixed by creating a pretty simple static table to store your dates in. Then with a cross join on your series and your dates you will get a product all of the dates for each of your series even though they are unrelated you still get use out of their product. Eg
Table IDs
ID 1 2 4 Dates
date 2007-04-01 00:00:00.000 2007-04-02 00:00:00.000 2007-04-03 00:00:00.000 You can now cross join (Select * from IDs Cross Join Dates)
1 2007-04-01 00:00:00.000 1 2007-04-02 00:00:00.000 1 2007-04-03 00:00:00.000 2 2007-04-01 00:00:00.000 2 2007-04-02 00:00:00.000 2 2007-04-03 00:00:00.000 4 2007-04-01 00:00:00.000 4 2007-04-02 00:00:00.000 4 2007-04-03 00:00:00.000 now that you have this nice neat Cartesian product you can include a left join to the table with your meat and get your statistics.
Select IDs.ID, Dates.Date, IsNull(Count(meat.id), 0) from IDs Cross Join Dates Left Join meat on meat.id = ids.ID and meat.date = dates.date Group by IDs.ID, Dates.Date
It works out pretty nice when aggregating data for 3 dimensional charting apps.
Try it out
Select
1 as ID into #ids
insert
into #ids values (2)
insert
into #ids values (3)
Select
'2007-04-01 00:00:00.000' as date into #dates
insert
into #dates values ('2007-04-02 00:00:00.000')
insert
into #dates values ('2007-04-03 00:00:00.000')
Select
1 as id, '2007-04-03 00:00:00.000' as date into #meat
insert
into #meat (id, date) values (1,'2007-04-03 00:00:00.000')
insert
into #meat (id, date) values (1,'2007-04-03 00:00:00.000')
insert
into #meat (id, date) values (1,'2007-04-01 00:00:00.000')
insert
into #meat (id, date) values (2,'2007-04-03 00:00:00.000')
insert
into #meat (id, date) values (2,'2007-04-03 00:00:00.000')
insert
into #meat (id, date) values (2,'2007-04-02 00:00:00.000')
insert
into #meat (id, date) values (2,'2007-04-02 00:00:00.000')
insert
into #meat (id, date) values (3,'2007-04-01 00:00:00.000')
Select
#ids.id, #dates.date, isnull(count(#meat.id),0) as ID_Date_Count
from
#ids cross join #dates
Left join #meat on #meat.id = #ids.id and #meat.date = #dates.date
Group
by #ids.id, #dates.date
Drop
table #ids
Drop
table #date
Drop
table #meat
Sorry for the bad markup but im not just #'n meat this is pretty useful.
-
Nonsense, how's this good for newbies? It immediately starts using 'join' and ',' without explaining these are short-hand notations, the layout of the actual SQL is bad and the table names are stupid.
It doesn't discuss basic concepts that I would expect on a 'refresher' e.g. multiple criteria in on clauses, nested selects, etc.
Also, where he says 'Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense. '
How does it make sense to randomly lop off the last two rows?
-
Hi Bojidar,
why do you claim this would be better:
SELECT *
FROM t1 a
LEFT JOIN t2 b ON a.f1=b.f1
WHERE b.f1 IS NULL
You didn't give any proof. Did you examine what the optimizer does with it?
My opinion: No good SQL. You should better reconsider this
cheers
jens
-
Dude,
this join ´ll work work fine for most instances, but put a row in T2 where F1 is null and u ll got a real problem.
select * from t1where t1.f1 not in (select t2.f1 from t2)
I suggest it
select * from t1where t1.f1 not in (select t2.f1 from t2 AND T2.F1 IS NOT NULL)
Bojidar,
I try to avoid outer joins because at least one table is full scanned. Not good for joining two big tables with workable indexes. For performance i guess the NOT EXISTS solutions above wins, but i can be wrong. Try two or more ways to do thing with a eye in the execution plan can be instructive. =)
-
I found this to be an informative and helpful refresher, thanks!
-
Good refresher.
Our company policy is to NOT use table aliases. The VP in charge of development came down hard. You have to use them in self joins though.
ATBCharles Kincaid
-
I'm still confused with them. I never know when to use which, I just experiment!
-
Mr. Jambu Krishnamurthy!
Excellent article.
Regards,
Ramaswamy
-
Mr. Jambu
As a newbee, I enjoyed the Article, however when I attempted to get the results for the last example on the FULL OUTER JOINS, I am unable to obtain the results you are showing, please provide me with the necessary joins to accompolish the example, Thanks
Observe the query and output carefully...and then see if you can get the following output, which you will agree makes more sense.
t1.f1 t2.f1 t3.f1===== ===== =====6 6 67 7 NULL8 8 NULL9 9 NULL10 10 NULLNULL 11 NULLNULL 12 12NULL 13 13NULL 14 NULLNULL 15 NULL5 NULL NULL4 NULL NULL3 NULL 32 NULL 21 NULL NULL
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply