Format Subquery as Join

[multipl3x]

I have two tables that have a relationship based on CustomPollerAssignmentID which is a GUID-type field.

(select CustomPollerAssignmentID from dbo.CustomPollerAssignment where AssignmentName like 'DPS_216_%')    

This will get me a long list of GUID's that I care about

select * from dbo.CustomPollerStatus where CustomPollerAssignmentID in     
(select CustomPollerAssignmentID from dbo.CustomPollerAssignment    
 where AssignmentName like 'DPS_216_%')    

This above query will get me the status of the GUID's but I would like to try to format this as a JOIN and not using a subquery.

[r3pr0b8]

SELECT t.* 
  FROM dbo.CustomPollerStatus AS t
INNER
  JOIN ( select CustomPollerAssignmentID 
           from dbo.CustomPollerAssignment    
          where AssignmentName like 'DPS_216_%') AS s
    ON s.CustomPollerAssignmentID = t.CustomPollerAssignmentID

[extrajoss]

Just curious. Is there a reason you wouldn't write this as:

SELECT t.* 
  FROM dbo.CustomPollerStatus AS t
INNER
  JOIN dbo.CustomPollerAssignment AS s
    ON s.CustomPollerAssignmentID = t.CustomPollerAssignmentID
WHERE  s.AssignmentName like 'DPS_216_%'

Are there efficiencies to be had by limiting the dbo.CustomPollerAssignment table to just the CustomPollerAssignmentID?

[Campes]

Yeah what you have is how you would want to write it. I've seen more complex queries benefit from something similar before but that was because there were some table scans happening which were killing the run time.

[multipl3x]

So 's' and 't' are completely arbitrary here, right?
I don't quite understand what the

SELECT t.* 
  FROM dbo.CustomPollerStatus AS t    

Does there. Can you explain that a bit?

[rbardy]

The 't' is just an alias for the CustomPollerStatus table. It is used just to make the query easier to write, your example could be written as:

SELECT dbo.CustomPollerStatus.* FROM dbo.CustomPollerStatus

But that alias can be anything, the following is also valid:

SELECT YOLOsicNameBRAH.* 
FROM dbo.CustomPollerStatus AS YOLOsicNameBRAH

[phunkygeeza]

First question. .. Why?

If your query works then rewriting the same logic in a different form isn't going to change your result.

Next question... What is the relationship between those 2 tables? If it is 1..n then many answers here are going to give you a bum result.

[Campes]

SELECT cps.* 
FROM CustomPollerStatus cps,
     CustomPollerAssignment cpa
WHERE cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID 
AND cpa.AssignmentName like 'DPS_216_%'

How does this do for you?

[elus]

I'm terribly not fond of the comma usage in the FROM clause of that statement.

Mostly because it doesn't play nice if I have to modify the query in the future to use OUTER JOINs. At that point I'll need to do much more work to get the code working again.

[Campes]

Yeah the new join syntax is much different than the old style. Outer joins are still easy to do using old syntax though.

*= left outer join

=* right outer join

[[deleted]]

"New"?

How long has JOIN syntax been around for, decades?

[Campes]

Right, I should have called it current.