All geek questions in one place

T-SQL - find differences for PK in one table (self join?)

My situation is as follows. I have a product table with pk "Parent" that has "Components". The data looks something like this.

Parent(PK)    Component
Car1          Wheel
Car1          Tyre
Car1          Roof
Car2          Alloy
Car2          Tyre 
Car2          Roof
Car3          Alloy
Car3          Tyre
Car3          Roof 
Car3          Leather Seats

Now what I want to do is some kind of query in which I can combine the two codes and see the differences ... IE If I load "Car1", "Car2", it will return something like:

Parent       Component
Car1         Wheel
Car2         Alloy

Like the difference between the two. If I said "Car1", "Car3", I would expect;

Parent       Component
Car1         Wheel
Car3         Alloy
Car3         Leather Seats

Your help in this matter will be greatly appreciated.

+3
source share
5 answers

Alternative

DECLARE @thisCar varchar(20) 
DECLARE @thatCar varchar(20) 

SET @thisCar = 'Car1'
SET @thatCar = 'Car2'


SELECT * FROM
(
    SELECT @thisCar AS Parent, Component FROM products WHERE parent = @thisCar
    EXCEPT
    SELECT @thisCar AS Parent, Component FROM products WHERE parent = @thatCar
) c1
UNION ALL
SELECT * FROM
(
    SELECT @thatCar AS Parent, Component FROM products WHERE parent = @thatCar
    EXCEPT
    SELECT @thatCar AS Parent, Component FROM products WHERE parent = @thisCar
    ) c2
+1
source

Without GROUP BY or UNION:

create table Products (
    Parent varchar(20) not null,
    Component varchar(20) not null
)
insert into Products (Parent,Component)
select 'Car1','Wheel' union all
select 'Car1','Tyre' union all
select 'Car1','Roof' union all
select 'Car2','Alloy' union all
select 'Car2','Tyre' union all
select 'Car2','Roof' union all
select 'Car3','Alloy' union all
select 'Car3','Tyre' union all
select 'Car3','Roof' union all
select 'Car3','Leather Seats'
go
select
    ISNULL (a.Parent,b.Parent) as Parent,
    ISNULL (a.Component,b.Component) as Component
from
    Products a
        full outer join
    Products b
        on
            a.Component = b.Component and
            a.Parent = 'Car1' and
            b.Parent = 'Car3'
where
    (a.Parent = 'Car1' and b.Parent is null) or
    (b.Parent = 'Car3' and a.Parent is null)
+4
source 
DECLARE @ThisCar .., @ThatCar;

SELECT @ThisCar = '...', @ThatCar = '...';

SELECT
    Parent, Component
FROM
    MyTable M1
WHERE
    M1.Parent = @ThisCar
    AND
    NOT EXISTS (SELECT *
        FROM
            MyTable M2
        WHERE
            M2.Parent = @ThatCar AND M1.Component = M2.Component)
UNION
SELECT
    Parent, Component
FROM
    MyTable M2
WHERE
    M2.Parent = @ThatCar 
    AND
    NOT EXISTS (SELECT *
        FROM
            MyTable M1
        WHERE
            M1.Parent = @ThisCar AND M1.Component = M2.Component):
+3

, :

Select Min(parent) As parent, component
From
(
    Select parent, component
    From products
    Where parent In ( 'Car1', 'Car3' )
)
Group By component
Having Count(*) = 1

Car1 Car2, Group By Having -clause , .

+2

, , :

select
    ISNULL (a.Parent,b.Parent) as Parent,
    ISNULL (a.Component,b.Component) as Component
from
    (select * from Products where Parent = 'Car1') as a
        full outer join
    (select * from Products where Parent = 'Car2') as b
        on
            a.Component = b.Component 
where
    (a.Parent = 'Car1' and b.Parent is null) or
    (b.Parent = 'Car2' and a.Parent is null)

30% , @gbns , @Damiens

, :

 
select
    ISNULL (a.Parent,b.Parent) as Parent,
    ISNULL (a.Component,b.Component) as Component
from
    (select * from Products where Parent = 'Car1') as a
        full outer join
    (select * from Products where Parent = 'Car2') as b


StmtText
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Compute Scalar(DEFINE:([Expr1012]=isnull([Expr1004],[Expr1010]), [Expr1013]=isnull([Expr1005],[Expr1011])))
       |--Filter(WHERE:([Expr1004]='Car1' AND [Expr1010] IS NULL OR [Expr1010]='Car2' AND [Expr1004] IS NULL))
            |--Hash Match(Full Outer Join, HASH:([CloudDb].[dbo].[Products].[Component])=([CloudDb].[dbo].[Products].[Component]), RESIDUAL:([CloudDb].[dbo].[Products].[Component]=[CloudDb].[dbo].[Products].[Component]))
                 |--Compute Scalar(DEFINE:([Expr1004]=[CloudDb].[dbo].[Products].[Parent], [Expr1005]=[CloudDb].[dbo].[Products].[Component]))
                 |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
                 |         |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car1') ORDERED FORWARD)
                 |         |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
                 |--Compute Scalar(DEFINE:([Expr1010]=[CloudDb].[dbo].[Products].[Parent], [Expr1011]=[CloudDb].[dbo].[Products].[Component]))
                      |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1006]))
                           |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car2') ORDERED FORWARD)
                           |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products]), SEEK:([Bmk1006]=[Bmk1006]) LOOKUP ORDERED FORWARD)

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT
    Parent, Component
FROM
    Products M1
WHERE
    M1.Parent = 'Car1'
    AND
    NOT EXISTS (SELECT *
        FROM
            Products M2
        WHERE
            M2.Parent = 'Car2' AND M1.Component = M2.Component)
UNION ALL
SELEC

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Concatenation
       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([M1].[Component]))
       |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |    |    |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts] AS [M1]), SEEK:([M1].[Parent]='Car1') ORDERED FORWARD)
       |    |    |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products] AS [M1]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1003]))
       |              |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts] AS [M2]), SEEK:([M2].[Parent]='Car2') ORDERED FORWARD)
       |              |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products] AS [M2]), SEEK:([Bmk1003]=[Bmk1003]),  WHERE:([CloudDb].[dbo].[Products].[Component] as [M1].[Component]=[CloudDb].[dbo].[Products].[Component] as [M2].[Component]) LOOKUP ORDERED FORWA
       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([M2].[Component]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1007]))
            |    |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts] AS [M2]), SEEK:([M2].[Parent]='Car2') ORDERED FORWARD)
            |    |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products] AS [M2]), SEEK:([Bmk1007]=[Bmk1007]) LOOKUP ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1010]))
                      |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts] AS [M1]), SEEK:([M1].[Parent]='Car1') ORDERED FORWARD)
                      |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products] AS [M1]), SEEK:([Bmk1010]=[Bmk1010]),  WHERE:([CloudDb].[dbo].[Products].[Component] as [M1].[Component]=[CloudDb].[dbo].[Products].[Component] as [M2].[Component]) LOOKUP ORDERED FORWA

StmtText
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SELECT * FROM
(
    SELECT 'Car1' AS Parent, Component FROM products WHERE parent = 'Car1'
    EXCEPT
    SELECT 'Car1'AS Parent, Component FROM products WHERE parent = 'Car2'
) c1
UNION ALL
SELECT * FROM
(
    SELECT 'Car2' AS Parent, Component 

StmtText
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Concatenation
       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([CloudDb].[dbo].[Products].[Component]))
       |    |--Sort(DISTINCT ORDER BY:([CloudDb].[dbo].[Products].[Component] ASC))
       |    |    |--Compute Scalar(DEFINE:([Expr1004]='Car1'))
       |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
       |    |              |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car1') ORDERED FORWARD)
       |    |              |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)
       |    |--Top(TOP EXPRESSION:((1)))
       |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1005]))
       |              |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car2') ORDERED FORWARD)
       |              |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products]), SEEK:([Bmk1005]=[Bmk1005]),  WHERE:([CloudDb].[dbo].[Products].[Component]=[CloudDb].[dbo].[Products].[Component]) LOOKUP ORDERED FORWARD)
       |--Nested Loops(Left Anti Semi Join, OUTER REFERENCES:([CloudDb].[dbo].[Products].[Component]))
            |--Sort(DISTINCT ORDER BY:([CloudDb].[dbo].[Products].[Component] ASC))
            |    |--Compute Scalar(DEFINE:([Expr1014]='Car2'))
            |         |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1010]))
            |              |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car1') ORDERED FORWARD)
            |              |--RID Lookup(OBJECT:([CloudDb].[dbo].[Products]), SEEK:([Bmk1010]=[Bmk1010]) LOOKUP ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1015]))
                      |--Index Seek(OBJECT:([CloudDb].[dbo].[Products].[idxProducts]), SEEK:([CloudDb].[dbo].[Products].[Parent]='Car2') ORDERED FORWARD)
                      | --RID Lookup (OBJECT: ([CloudDb]. [Dbo]. [Products]), SEEK: ([Bmk1015] = [Bmk1015]), WHERE: ([CloudDb]. [Dbo]. [Products]. [ Component] = [CloudDb]. [Dbo]. [Products]. [Component]) LOOKUP ORDERED FORWARD)

A slightly more detailed query can sometimes lead to a simpler execution plan.

0
source

Source: https://habr.com/ru/post/1748115/

More articles:


All Articles