UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Joins Without Joins!, Older SQL Server Versions    
 
   
duggie
post Oct 21 2017, 05:33 AM
Post#1



Posts: 405
Joined: 14-October 12



This query works but I don't understand it:

CODE
select
    d.directorid,
    d.directorname,
    f.filmname,
    f.filmdirectorid
from
    tbldirector as d
    JOIN tblFilm as f
        on d.directorid=f.FilmDirectorID


instead, I prefer this version:

CODE
select
    tbldirector.directorid,
    tbldirector.directorname,
    tblfilm.filmname,
    tblfilm.filmdirectorid
from
    tbldirector, tblFilm  
where
    tbldirector.directorid=tblfilm.FilmDirectorID



so if I want to write this query, without using joins:

CODE
select
    d.directorid,
    d.directorname,
    f.filmname,
    f.filmdirectorid
from
    tbldirector as d
    LEFT JOIN tblFilm as f
        on d.directorid=f.FilmDirectorID


can it be done?

Thanks
Go to the top of the page
 
GroverParkGeorge
post Oct 21 2017, 05:51 AM
Post#2


UA Admin
Posts: 31,201
Joined: 20-June 02
From: Newcastle, WA


Hm. What is the logic behind the preference for the "Where" style join over the standard join syntax? As I understand it, SQL Server executes them both exactly the same. It may be more comfortable or perhaps more clear to you, of course.

That said:

SQL
select
d.directorid,
d.directorname,
f.filmname,
f.filmdirectorid
from tbldirector as d
, tblFilm as f
WHERE d.directorid=f.FilmDirectorID OR f.FilmDirectorID IS NULL

--------------------
Go to the top of the page
 
duggie
post Oct 21 2017, 05:54 AM
Post#3



Posts: 405
Joined: 14-October 12



I find the query using where easier to understand.

Your query does not produce the same result as:

CODE
select
    d.directorid,
    d.directorname,
    f.filmname,
    f.filmdirectorid
from
    tbldirector as d
    left join tblFilm as f
        on d.directorid=f.FilmDirectorID


your query results returned more results, though not sure if all mine were contained in yours.
Go to the top of the page
 
nvogel
post Oct 21 2017, 07:33 AM
Post#4



Posts: 811
Joined: 26-January 14
From: London, UK


All your queries use joins, they just use different syntax.

There is no alternative syntax for outer joins. An outer join is effectively a UNION and you can replace it with something like the following. A better option would be to get used to the LEFT OUTER JOIN syntax.

SELECT d.directorid, d.directorname, f.filmname, f.filmdirectorid
FROM tbldirector AS d, tblFilm AS f
WHERE d.directorid = f.FilmDirectorID
UNION
SELECT d.directorid, d.directorname, null filmname, null filmdirectorid
FROM tbldirector d
WHERE NOT EXISTS (SELECT 1 FROM tblFilm AS f WHERE d.directorid=f.FilmDirectorID);
Go to the top of the page
 
duggie
post Oct 21 2017, 08:19 AM
Post#5



Posts: 405
Joined: 14-October 12



Thanks but what does 1 represent, as in SELECT 1:

CODE
WHERE NOT EXISTS (SELECT 1 FROM tblFilm AS f WHERE d.directorid=f.FilmDirectorID);
Go to the top of the page
 
nvogel
post Oct 21 2017, 10:14 AM
Post#6



Posts: 811
Joined: 26-January 14
From: London, UK


EXISTS returns true if the subquery returns at least one row; false if zero rows. The content of the row being returned doesn't matter, so 1 is just a dummy value for the row returned (if any).
Go to the top of the page
 
duggie
post Oct 22 2017, 04:04 PM
Post#7



Posts: 405
Joined: 14-October 12



Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    13th December 2017 - 05:21 PM