SQL Inner Join Vs SubQueries

Hi EE,

Just curious why my two below queries are returning different result sets and wondering which result set is correct

This query returns 64619 rows
 SELECT DISTINCT  Suppliers.SupplierName ,
                        Bookings.JobNumber
               FROM     dbo.VW_CabVistaBookings AS Bookings
                        INNER JOIN Space.DimUkPostcode AS PickUpPostCode ON PickUpPostCode.Id = Bookings.DimPickupPostcodeId
                        INNER JOIN Core.DimDate AS PickUpDates ON PickUpDates.Id = Bookings.DimPickupDateId
                        INNER JOIN Space.DimUkPostcode AS DropOffPostCode ON DropOffPostCode.Id = Bookings.DimDropoffPostcodeId
                        INNER JOIN Legacy.DimCabvistaSupplier AS Suppliers ON Suppliers.Id = Bookings.DimCabvistaSupplierId
               WHERE    PickUpDates.CalendarDate BETWEEN '20150101'
                                                 AND     '20151231'
                        AND ( ( PickUpPostCode.Outcode IN (
                              SELECT    OutCode
                              FROM      dbo.LondonPostCodes )
                              AND PickUpPostCode.Incode IN (
                              SELECT    Incode
                              FROM      dbo.LondonPostCodes )))
   

Open in new window



this query is more slower and returns 62634
 SELECT DISTINCT
  Suppliers.SupplierName ,
        Bookings.JobNumber
 FROM   dbo.VW_CabVistaBookings AS Bookings
        INNER JOIN Space.DimUkPostcode AS PickUpPostCode ON PickUpPostCode.Id = Bookings.DimPickupPostcodeId
        INNER JOIN Core.DimDate AS PickUpDates ON PickUpDates.Id = Bookings.DimPickupDateId
        INNER JOIN Space.DimUkPostcode AS DropOffPostCode ON DropOffPostCode.Id = Bookings.DimDropoffPostcodeId
        INNER JOIN Legacy.DimCabvistaSupplier AS Suppliers ON Suppliers.Id = Bookings.DimCabvistaSupplierId
        INNER JOIN dbo.LondonPostCodes AS LondonPostCodes ON PickUpPostCode.Incode = LondonPostCodes.Incode
                                                             AND PickUpPostCode.Outcode = LondonPostCodes.OutCode
 WHERE  PickUpDates.CalendarDate BETWEEN '20150101'
                                 AND     '20151231';

Open in new window



There is difference of 1985 records.

Are both queries not supposed to return same no of rows? should inner join query not be faster?

regards
Ali ShahSQL DeveloperAsked:
Who is Participating?
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Are both queries not supposed to return same no of rows?
No. They are different.
In the first query you're requesting:
PickUpPostCode.Outcode IN (SELECT OutCode FROM dbo.LondonPostCodes)
AND PickUpPostCode.Incode IN (SELECT Incode FROM dbo.LondonPostCodes)

Meaning that you want all records that has the same OutCode from PickUpPostCode and LondonPostCodes and also all records that has the same Incode from PickUpPostCode and Incode.

In the second query you're requesting:
NNER JOIN dbo.LondonPostCodes AS LondonPostCodes ON PickUpPostCode.Incode = LondonPostCodes.Incode AND PickUpPostCode.Outcode = LondonPostCodes.OutCode
Meaning that you only want records that has the same OutCode and InCode simultaneously from PickUpPostCode and LondonPostCodes.


should inner join query not be faster?
What are the queries performance? Can you provide both Query Execution Plans?
0
 
Pawan KumarDatabase ExpertCommented:
Compare these now.. If these yields the same output then NULL is the issue.

1..

 SELECT DISTINCT  Suppliers.SupplierName ,
                        Bookings.JobNumber
               FROM     dbo.VW_CabVistaBookings AS Bookings
                        INNER JOIN Space.DimUkPostcode AS PickUpPostCode ON PickUpPostCode.Id = Bookings.DimPickupPostcodeId
                        INNER JOIN Core.DimDate AS PickUpDates ON PickUpDates.Id = Bookings.DimPickupDateId
                        INNER JOIN Space.DimUkPostcode AS DropOffPostCode ON DropOffPostCode.Id = Bookings.DimDropoffPostcodeId
                        INNER JOIN Legacy.DimCabvistaSupplier AS Suppliers ON Suppliers.Id = Bookings.DimCabvistaSupplierId
               WHERE    ( PickUpDates.CalendarDate BETWEEN '20150101' AND  '20151231' )
                        AND  ( PickUpPostCode.Outcode IN (
                              SELECT    OutCode
                              FROM      dbo.LondonPostCodes ))

                        AND ( PickUpPostCode.Incode IN (
                              SELECT    Incode
                              FROM      dbo.LondonPostCodes ))

Open in new window



2....
   
SELECT DISTINCT
  Suppliers.SupplierName ,
        Bookings.JobNumber
 FROM   dbo.VW_CabVistaBookings AS Bookings
        INNER JOIN Space.DimUkPostcode AS PickUpPostCode ON PickUpPostCode.Id = Bookings.DimPickupPostcodeId
        INNER JOIN Core.DimDate AS PickUpDates ON PickUpDates.Id = Bookings.DimPickupDateId
        INNER JOIN Space.DimUkPostcode AS DropOffPostCode ON DropOffPostCode.Id = Bookings.DimDropoffPostcodeId
        INNER JOIN Legacy.DimCabvistaSupplier AS Suppliers ON Suppliers.Id = Bookings.DimCabvistaSupplierId
        INNER JOIN dbo.LondonPostCodes AS LondonPostCodes ON PickUpPostCode.Incode = LondonPostCodes.Incode
                                                             AND PickUpPostCode.Outcode = LondonPostCodes.OutCode
 WHERE  PickUpDates.CalendarDate BETWEEN '20150101'
                                 AND     '20151231';

Open in new window

0
 
Ali ShahSQL DeveloperAuthor Commented:
They are producing the same old result with the difference of 1985. and i could not see any difference in your queries from my original queries
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Pawan KumarDatabase ExpertCommented:
Join one is the correct right if we consider the results..
0
 
Ali ShahSQL DeveloperAuthor Commented:
i don't know as both of them are still producing the same old result
0
 
Pawan KumarDatabase ExpertCommented:
I am talking about your 2 queries//
0
 
Ali ShahSQL DeveloperAuthor Commented:
Again i am confused about my 2 queries that's why i asked for help. I think both of them should return same result. But they are not and i don;t know whether join one is right or the subquery one
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
In below condition for a single row we are matching the Incode and Outcode from two tables LondonPostCodes and PickUpPostCode

INNER JOIN dbo.LondonPostCodes AS LondonPostCodes ON PickUpPostCode.Incode = LondonPostCodes.Incode
                                                             AND PickUpPostCode.Outcode = LondonPostCodes.OutCode

And in the below condition this is not the case. Thats the issue

                        AND ( ( PickUpPostCode.Outcode IN (
                              SELECT    OutCode
                              FROM      dbo.LondonPostCodes )
                              AND PickUpPostCode.Incode IN (
                              SELECT    Incode
                              FROM      dbo.LondonPostCodes )))
   


Note -  In clause will be evaluated first and then the outer query. In clause also create issues if you have NULL values coming from the inner query.

Join - Evaluated Row by Row.

Hope it helps !!
0
 
Ali ShahSQL DeveloperAuthor Commented:
Ah thanks both of you. It explains the situation. I think in my case i need the Inner Join query as i want to match simultaneously.
I have created indexes on incode and outcode and it has improved the performance.

regards,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.