Ali Shah
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
this query is more slower and returns 62634
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
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 )))
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';
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
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..
ASKER
i don't know as both of them are still producing the same old result
I am talking about your 2 queries//
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,
I have created indexes on incode and outcode and it has improved the performance.
regards,
1..
Open in new window
2....
Open in new window