May 18, 2018 at 9:15 am
I have never seen this before and it goes against everything that I know about SQL data sets. I have some pretty gnarly code that I won't share for now, When I JOIN in the query I get 125K records, When I use a LEFT JOIN I get 117K records. The JOIN predicate is on a single indexed field in both tables . I'm just trying to reconcile the two datasets but it is giving Excel a heartattack trying to do 250K Vlookups 🙁
May 18, 2018 at 9:24 am
aaron.reese - Friday, May 18, 2018 9:15 AMI have never seen this before and it goes against everything that I know about SQL data sets. I have some pretty gnarly code that I won't share for now, When I JOIN in the query I get 125K records, When I use a LEFT JOIN I get 117K records. The JOIN predicate is on a single indexed field in both tables . I'm just trying to reconcile the two datasets but it is giving Excel a heartattack trying to do 250K Vlookups 🙁
If you are looking to comare the two tables, are they identical in structure? They don't have to have identical column names, but if they have identical data types for each column that can map to the other table, then you could use EXCEPT twice to get a good comparison.
SELECT col1, col2, col3, ... col9
FROM TableA
EXCEPT
SELECT col1, col2, col3, ... col9
FROM TableB
-- and now the other way to ensure we know the exact differences from both points of view
SELECT col1, col2, col3, ... col9
FROM TableB
EXCEPT
SELECT col1, col2, col3, ... col9
FROM TableA
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 9:42 am
No, I am joining two tables which have a common single field PK-FK relationship (should also be 1:1)
JOIN TableA ON TableA.ID = TableB.TabelA_ID = 125K records
LEFT JOIN TableA ON TableA.ID = TableB.TabelA_ID = 117K records
Same predicates, same filters
The original query has lots of LEFT JOINS and I am trying to improve the query performance by ensuring I can use JOINS as far down as possible.
May 18, 2018 at 11:31 am
Is it possible that the LEFT JOIN is producing a different execution plan which just happens to be exposing some other unknown issue in your data? Perhaps a join to a view that produces inconsistent results from run to run depending on execution plan? Something like that? For example, I've seen things like that happen before, where there was a "SELECT TOP X" or ROW_NUMBER() involved with an insufficient ORDER BY, and it happened to produce certain results in some cases and other results in other cases. Just a thought.
May 18, 2018 at 11:55 am
aaron.reese - Friday, May 18, 2018 9:42 AMNo, I am joining two tables which have a common single field PK-FK relationship (should also be 1:1)JOIN TableA ON TableA.ID = TableB.TabelA_ID = 125K records
LEFT JOIN TableA ON TableA.ID = TableB.TabelA_ID = 117K recordsSame predicates, same filters
The original query has lots of LEFT JOINS and I am trying to improve the query performance by ensuring I can use JOINS as far down as possible.
I'll have to agree with autoexcrement, as without the actual query or actual execution plans, there's no way to see what's going on, so all we can do is guess. Do check out the suggestions autoexcrement made and see if perhaps there's a view that contains a TOP without sufficient ORDER BY and as such produces inconsistent results. Or ... post your query(s?) and execution plans.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 12:03 pm
aaron.reese - Friday, May 18, 2018 9:42 AMNo, I am joining two tables which have a common single field PK-FK relationship (should also be 1:1)JOIN TableA ON TableA.ID = TableB.TabelA_ID = 125K records
LEFT JOIN TableA ON TableA.ID = TableB.TabelA_ID = 117K recordsSame predicates, same filters
The original query has lots of LEFT JOINS and I am trying to improve the query performance by ensuring I can use JOINS as far down as possible.
Without seeing the query it's hard to say but there are different scenarios where an outer join can result in few rows. Is the where clause filtering on the left joined table?
Sue
May 18, 2018 at 12:09 pm
OP claims "Same predicates, same filters"
May 18, 2018 at 12:14 pm
autoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"
That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
May 18, 2018 at 12:14 pm
autoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"
Yes, but a filter that can cause unreliable results isn't going to help. That's why we really need to see the query and execution plan.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 18, 2018 at 2:11 pm
Sue_H - Friday, May 18, 2018 12:14 PMautoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
Barring something like the situation I suggested, I can't see how that would cause more records to appear in an INNER JOIN than an identical LEFT JOIN. But maybe I'm missing something. We definitely need to know more about this SQL to be of any real help.
May 18, 2018 at 2:20 pm
autoexcrement - Friday, May 18, 2018 2:11 PMSue_H - Friday, May 18, 2018 12:14 PMautoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
Barring something like the situation I suggested, I can't see how that would cause more records to appear in an INNER JOIN than an identical LEFT JOIN. But maybe I'm missing something. We definitely need to know more about this SQL to be of any real help.
It absolutely can. Here is a pretty good walk through demonstrating, explaining this behavior:
Criteria on Outer Joined Tables
You can search and find more posts, articles. Interesting reading if nothing else. Try searching on: left join fewer rows
You get more hits on that than outer join
Sue
May 18, 2018 at 2:44 pm
Sue_H - Friday, May 18, 2018 2:20 PMautoexcrement - Friday, May 18, 2018 2:11 PMSue_H - Friday, May 18, 2018 12:14 PMautoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
Barring something like the situation I suggested, I can't see how that would cause more records to appear in an INNER JOIN than an identical LEFT JOIN. But maybe I'm missing something. We definitely need to know more about this SQL to be of any real help.
It absolutely can. Here is a pretty good walk through demonstrating, explaining this behavior:
Criteria on Outer Joined TablesYou can search and find more posts, articles. Interesting reading if nothing else. Try searching on: left join fewer rows
You get more hits on that than outer joinSue
Thanks for the links. At the risk of hijacking this thread, I still don't exactly understand what you mean though. The example in the link, and in the first few results on your suggested search, seem to be about why a left join is behaving like an inner join--more specifically, "left join results in fewer rows than in left table"--not about left joins that return fewer results than an identical query with an inner join. I understand the implications of putting conditions in the WHERE clause rather than in the join when using outer joins, but it seems that worst-case it would turn the outer join into an inner join. The OP is asking about something slightly different, unless I am missing something, which is entirely possible. Apologies.
May 18, 2018 at 3:29 pm
autoexcrement - Friday, May 18, 2018 2:44 PMSue_H - Friday, May 18, 2018 2:20 PMautoexcrement - Friday, May 18, 2018 2:11 PMSue_H - Friday, May 18, 2018 12:14 PMautoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
Barring something like the situation I suggested, I can't see how that would cause more records to appear in an INNER JOIN than an identical LEFT JOIN. But maybe I'm missing something. We definitely need to know more about this SQL to be of any real help.
It absolutely can. Here is a pretty good walk through demonstrating, explaining this behavior:
Criteria on Outer Joined TablesYou can search and find more posts, articles. Interesting reading if nothing else. Try searching on: left join fewer rows
You get more hits on that than outer joinSue
Thanks for the links. At the risk of hijacking this thread, I still don't exactly understand what you mean though. The example in the link, and in the first few results on your suggested search, seem to be about why a left join is behaving like an inner join--more specifically, "left join results in fewer rows than in left table"--not about left joins that return fewer results than an identical query with an inner join. I understand the implications of putting conditions in the WHERE clause rather than in the join when using outer joins, but it seems that worst-case it would turn the outer join into an inner join. The OP is asking about something slightly different, unless I am missing something, which is entirely possible. Apologies.
Without seeing the actual code it is really hard to know what is happening. This is really one of those cases where seeing it will definitely help in understanding. Even then, we may need some sample data that also reflects the problem as well.
May 18, 2018 at 3:57 pm
autoexcrement - Friday, May 18, 2018 2:44 PMSue_H - Friday, May 18, 2018 2:20 PMautoexcrement - Friday, May 18, 2018 2:11 PMSue_H - Friday, May 18, 2018 12:14 PMautoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"That's why I asked which table - due to the issue when the outer table is filtered in the where clause.
Sue
Barring something like the situation I suggested, I can't see how that would cause more records to appear in an INNER JOIN than an identical LEFT JOIN. But maybe I'm missing something. We definitely need to know more about this SQL to be of any real help.
It absolutely can. Here is a pretty good walk through demonstrating, explaining this behavior:
Criteria on Outer Joined TablesYou can search and find more posts, articles. Interesting reading if nothing else. Try searching on: left join fewer rows
You get more hits on that than outer joinSue
Thanks for the links. At the risk of hijacking this thread, I still don't exactly understand what you mean though. The example in the link, and in the first few results on your suggested search, seem to be about why a left join is behaving like an inner join--more specifically, "left join results in fewer rows than in left table"--not about left joins that return fewer results than an identical query with an inner join. I understand the implications of putting conditions in the WHERE clause rather than in the join when using outer joins, but it seems that worst-case it would turn the outer join into an inner join. The OP is asking about something slightly different, unless I am missing something, which is entirely possible. Apologies.
Yeah..that wasn't the best example. I thought the others had the issues with nulls/data issues, the joins, comparing with a faulty/incorrect/<whatever word> inner join, etc
Sorry, I'll bow out - don't think I can say much without seeing the query and the properties on the joining columns for the tables involved.
Sue
May 18, 2018 at 4:46 pm
autoexcrement - Friday, May 18, 2018 12:09 PMOP claims "Same predicates, same filters"
I've seen many such claims in the past be not so true. We need to see the actual queries.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply