[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Inner Join Vs SubQueries

Posted on 2016-11-26
9
Medium Priority
?
79 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 37

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 37

Expert Comment

by:Pawan Kumar
ID: 41902363
Join one is the correct right if we consider the results..
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

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

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 37

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 1000 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 53

Accepted Solution

by:
Vitor Montalvão earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

830 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