Solved

SQL Inner Join Vs SubQueries

Posted on 2016-11-26
9
54 Views
Last Modified: 2016-12-28
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
0
Comment
Question by:shah36
  • 4
  • 4
9 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41902340
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
 

Author Comment

by:shah36
ID: 41902358
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41902363
Join one is the correct right if we consider the results..
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:shah36
ID: 41902364
i don't know as both of them are still producing the same old result
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41902365
I am talking about your 2 queries//
0
 

Author Comment

by:shah36
ID: 41902368
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
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41902386
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
 
LVL 48

Accepted Solution

by:
Vitor Montalvão earned 250 total points
ID: 41903724
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
 

Author Closing Comment

by:shah36
ID: 41903809
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question