Table Joining

• NikosV

  SSCrazy

  Points: 2111

  More actions

  September 20, 2018 at 12:56 pm

  #368347

  Hi guys,

  Just a quick question.

  Below I'm joining onto a table with a WHERE clause in two different ways and I'm wondering what is generally considered a "better" option. When I say better option, I mean an overall better option.

  First Way (Normal Way)
  
  
SELECT A.FirstName, A.LastName, B.Location
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.PersonId = B.PersonId

  WHERE B.Location = 'London'


  Second Way (Joining onto a derived table)
  
SELECT A.FirstName, A.LastName, GetLocation.Location
FROM TableA
LEFT OUTER JOIN (SELECT PersonId, Location FROM TableB WHERE Location = 'London')GetLocation
ON A.PersonId = GetLocation.PersonId


  I as a beginner, somehow, started off by using the second way because I believed that joining onto a derived table "targeted" more specifically the columns I was interested in and that it "saved" scanning other columns. In other words I believed that being more explicit was better. Later on, I started using the first way. The first way seems to give me more flexibility if later on in my query I need the values of other columns. 

  Now I'm just confused. So between the first way and the second way, are there really any actual differences and if there are, what are they. Are they important differences? Does it matter which way I choose? What should I generally have in mind when performing joins?

  I'd appreciate some general feedback or perhaps some rules of thumb to keep in mind when performing joins. I've bought Itzik Ben Gans book on T-SQL Fundamentals Third Edition, read through the joins part and have generally understood the concept but things like the examples above are obviously things that probably need to be asked.

  Any feedback appreciated.

  Thanks guys.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 20, 2018 at 3:05 pm

  #2006498

  NikosV - Thursday, September 20, 2018 12:56 PM

  Hi guys,

  Just a quick question.

  Below I'm joining onto a table with a WHERE clause in two different ways and I'm wondering what is generally considered a "better" option. When I say better option, I mean an overall better option.

  First Way (Normal Way)
  
  
SELECT A.FirstName, A.LastName, B.Location
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.PersonId = B.PersonId

  WHERE B.Location = 'London'


  Second Way (Joining onto a derived table)
  
SELECT A.FirstName, A.LastName, GetLocation.Location
FROM TableA
LEFT OUTER JOIN (SELECT PersonId, Location FROM TableB WHERE Location = 'London')GetLocation
ON A.PersonId = GetLocation.PersonId


  I as a beginner, somehow, started off by using the second way because I believed that joining onto a derived table "targeted" more specifically the columns I was interested in and that it "saved" scanning other columns. In other words I believed that being more explicit was better. Later on, I started using the first way. The first way seems to give me more flexibility if later on in my query I need the values of other columns. 

  Now I'm just confused. So between the first way and the second way, are there really any actual differences and if there are, what are they. Are they important differences? Does it matter which way I choose? What should I generally have in mind when performing joins?

  I'd appreciate some general feedback or perhaps some rules of thumb to keep in mind when performing joins. I've bought Itzik Ben Gans book on T-SQL Fundamentals Third Edition, read through the joins part and have generally understood the concept but things like the examples above are obviously things that probably need to be asked.

  Any feedback appreciated.

  Thanks guys.

  First, run the queries and compare the output, you should notice that they are different.

  The first query is actually an INNER JOIN as you are filtering on the B.Location column being equal to 'London'.  This will only be true where there is data from TableB.

  The second query will return a rows of data from TableA plus any data from TableB where B.Location is equal to 'London'.

  You could also write the second query like this:
  
SELECT
A.FirstName
, A.LastName
, B.Location
FROM
TableA A
LEFT OUTER JOIN TableB B
  ON A.PersonId = B.PersonId and
   B.Location = 'London';


• drew.allen

  SSC Guru

  Points: 76985

  More actions

  September 20, 2018 at 3:10 pm

  #2006499

  NikosV - Thursday, September 20, 2018 12:56 PM

  Hi guys,

  Just a quick question.

  Below I'm joining onto a table with a WHERE clause in two different ways and I'm wondering what is generally considered a "better" option. When I say better option, I mean an overall better option.

  First Way (Normal Way)
  
  
SELECT A.FirstName, A.LastName, B.Location
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.PersonId = B.PersonId

  WHERE B.Location = 'London'


  Second Way (Joining onto a derived table)
  
SELECT A.FirstName, A.LastName, GetLocation.Location
FROM TableA
LEFT OUTER JOIN (SELECT PersonId, Location FROM TableB WHERE Location = 'London')GetLocation
ON A.PersonId = GetLocation.PersonId


  I as a beginner, somehow, started off by using the second way because I believed that joining onto a derived table "targeted" more specifically the columns I was interested in and that it "saved" scanning other columns. In other words I believed that being more explicit was better. Later on, I started using the first way. The first way seems to give me more flexibility if later on in my query I need the values of other columns. 

  Now I'm just confused. So between the first way and the second way, are there really any actual differences and if there are, what are they. Are they important differences? Does it matter which way I choose? What should I generally have in mind when performing joins?

  I'd appreciate some general feedback or perhaps some rules of thumb to keep in mind when performing joins. I've bought Itzik Ben Gans book on T-SQL Fundamentals Third Edition, read through the joins part and have generally understood the concept but things like the examples above are obviously things that probably need to be asked.

  Any feedback appreciated.

  Thanks guys.

  The two are different and will give different results, so which one you use depends on which results you want.  The first one, the WHERE clause converts your outer join to an inner join, so you should use the inner join instead of this particular one if it produces the results you want.

  The second one also has an alternate form that is much simpler for relatively easy criteria.  You will probably still want to use your original form if the WHERE clause gets too complex, and you should only use this form with outer joins.  An inner join, it is much better to clearly separate the join conditions from the filter conditions.

  Here is sample data.

  CREATE TABLE #Person
(
    PersonID  TINYINT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
)

  INSERT #Person(PersonID, FirstName, LastName)
VALUES (1, 'Paul', 'McCartney'), (2, 'John', 'Lennon'), (3, 'George', 'Harrison'), (4, 'Ringo', 'Starr')

  CREATE TABLE #PersonLocations(
(
    PersonID TINYINT FOREIGN KEY REFERENCES #Person(PersonID),
    PersonLocation VARCHAR(50)
)

  INSERT #PersonLocations
VALUES(1, 'London'), (1, 'Liverpool'), (3, 'Liverpool'), (4, 'London')

  SELECT *
FROM #Person p
LEFT OUTER JOIN #PersonLocations pl
    ON p.PersonID = pl.PersonID
WHERE pl.PersonLocation = 'London'

  SELECT *
FROM #Person p
INNER JOIN #PersonLocations pl
    ON p.PersonID = pl.PersonID
WHERE pl.PersonLocation = 'London'

  SELECT *
FROM #Person p
LEFT OUTER JOIN ( SELECT pl.PersonID, pl.PersonLocation FROM #PersonLocations pl WHERE pl.PersonLocation = 'London' ) l
    ON p.PersonID = l.PersonID

  SELECT *
FROM #Person p
LEFT OUTER JOIN #PersonLocations pl
    ON p.PersonID = pl.PersonID
        AND pl.PersonLocation = 'London'

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• andycadley

  SSCertifiable

  Points: 5296

  More actions

  September 20, 2018 at 8:42 pm

  #2006513

  I'd go with Lynn's suggestion of putting it in the ON clause of the join conditions, it's the cleanest syntax whilst avoiding accidentally converting an OUTER join into an INNER one as in the first variant posted.

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  September 21, 2018 at 9:49 am

  #2006597

  As to the query plans for these:
  
SELECT A.FirstName, A.LastName, B.Location
FROM TableA A
LEFT OUTER JOIN TableB B
ON A.PersonId = B.PersonId AND B.Location = 'London'

  SELECT A.FirstName, A.LastName, GetLocation.Location
FROM TableA
LEFT OUTER JOIN (SELECT PersonId, Location FROM TableB WHERE Location = 'London')GetLocation
ON A.PersonId = GetLocation.PersonId

  
  I'd expect them to be identical.

  That is, the first syntax is cleaner, so use it, since the second is not needed to help the optimizer create the best plan.  In these cases, the optimizer will figure all that out on its own.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".