CODEWITHSUNDEEP
sqlsql-server
h33
h33

Reputation: 1344

Accessing derived tables from outer query

In the following problem Filtering based on Joining Multiple Tables in SQL

I managed to determine that the posters problem was happening because he was accessing derived tables from the outer query.

What I don't understand is why this happened.

So if you run the following

create table salesperson (
    id int, name varchar(40)
)
create table customer (
    id int, name varchar(40)
)
create table orders (
    number int, cust_id int, salesperson_id int
)
insert into salesperson values (1, 'abe'); insert into salesperson values (2, 'bob');
insert into salesperson values (5, 'chris'); insert into salesperson values (7, 'dan');
insert into salesperson values (8, 'ken'); insert into salesperson values (11, 'joe'); 
insert into customer values (4, 'Samsonic'); insert into customer values (6, 'panasung');
insert into customer values (7, 'samony'); insert into customer values (9, 'orange');
insert into orders values (10, 4, 2); insert into orders values (20, 4, 8);
insert into orders values (30, 9, 1); insert into orders values (40, 7, 2);
insert into orders values (50, 6, 7); insert into orders values (60, 6, 7);
insert into orders values (70, 9, 7);  

SELECT *
FROM salesperson s 
INNER JOIN orders o ON s.id = o.salesperson_id
INNER JOIN customer c ON o.cust_id = c.id
WHERE s.name NOT IN (
    select s.name where c.name='Samsonic'
)

SELECT *
FROM salesperson s 
INNER JOIN orders o ON s.id = o.salesperson_id
INNER JOIN customer c ON o.cust_id = c.id
WHERE s.name NOT IN (
    SELECT s.name
    FROM  salesperson s 
    INNER JOIN orders o ON s.id = o.salesperson_id
    INNER JOIN customer c ON o.cust_id = c.id
    WHERE c.name = 'Samsonic'
)

The first select statement accesses the derived tables in the outer query, while the other creates its own joins and derives its own tables.

Why does the first select contain bob while the other one does not?

Upvotes: 0

Views: 78

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Your first query has a select with no from clause. So the where is equivalent to:

WHERE s.name NOT IN (CASE WHEN c.name = 'Samsonic' THEN s.name END)

Or more simply:

WHERE c.name <> 'Samsonic'

Bob has an order that is not with 'Samsonic', so Bob is in the result set. In other words, the logic is looking at each row individually.

The second version is looking at all names that have made an order. Bob is one of those names, so this applies to all orders made by Bob.

If you want to exclude all salespersons who have ever made an order to 'Samsonic', then I would recommend using window functions instead of complicated logic:

SELECT *
FROM (SELECT s.id as salesperson_id, s.name as salesperson_name, c.id as customer_id, c.name as customer_name, o.number,
             SUM(CASE WHEN c.name = 'Samsonic' THEN 1 ELSE 0 END) OVER (PARTITION BY s.id) as num_samsonic
      FROM salesperson s INNER JOIN
           orders o 
           ON s.id = o.salesperson_id INNER JOIN
           customer c
           ON o.cust_id = c.id
      WHERE c.name <> 'Samsonic'
     ) soc
WHERE num_samsonic = 0

Upvotes: 1

Dale K
Dale K

Reputation: 27226

The difference is that in your first query you are only removing orders which involve Samsonic, because the exclusion is only looking at data in the current row. Whereas by the sounds of it you want to remove any sales-person who has ever sold a Samsonic. You can see the difference with in the results of the following query:

SELECT *, s.name, c.name
  , case when s.name NOT IN (
    select s.name where c.name='Samsonic'
) then 1 else 0 end /* Order not Samsonic */
  , case when not exists (
    select 1
    from Orders O1
    inner join Customer C1 on o1.cust_id = c1.id
    where C1.Name = 'Samsonic' and o1.salesperson_id = O.salesperson_id
  ) then 1 else 0 end /* Salesperson never sold a Samsonic */
FROM salesperson s 
INNER JOIN orders o ON s.id = o.salesperson_id
INNER JOIN customer c ON o.cust_id = c.id

Upvotes: 1

Ajan Balakumaran
Ajan Balakumaran

Reputation: 1649

In your first query you are only removing the rows which has customer name Samsonic, since Bob has a record for samony that one comes in the out put.

In the second one you are getting the salesperson who has the customer name Samsonic in that case you are getting both Bob and Ken then you are removing all there records for both Bob and Ken using the 'not in'so both records for bob is getting removed hence you dont get any.

Upvotes: 1

Related Questions