Link to home
Start Free TrialLog in
Avatar of Ali Shah
Ali ShahFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

Avatar of Ali Shah

ASKER

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
Join one is the correct right if we consider the results..
i don't know as both of them are still producing the same old result
I am talking about your 2 queries//
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
SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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,