?
Solved

SQL Inner Join Vs SubQueries

Posted on 2016-11-26
9
Medium Priority
?
68 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 29

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 29

Expert Comment

by:Pawan Kumar
ID: 41902363
Join one is the correct right if we consider the results..
0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 

Author Comment

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

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 29

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 51

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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

752 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