ANTI-JOIN with two or more columns.

Consider the following schema:
CREATE TABLE A(i int, j int)
CREATE TABLE B(i int, j int)
INSERT A VALUES(1,1)
INSERT A VALUES(1,2)
INSERT A VALUES(2,1)
INSERT A VALUES(2,2)
INSERT A VALUES(2,3)
INSERT B VALUES(3,1)
INSERT B VALUES(3,2)
INSERT B VALUES(1,1)
INSERT B VALUES(1,2)
INSERT B VALUES(2,2)
INSERT B VALUES(4,5)
INSERT B VALUES(5,4)
How do I compose a TSQL Query that will list the records in Table B, that DO
NOT exist in Table A.?
I would like a record set that looks like this:
i j
-- --
3 1
3 2
4 5
5 4
--
Bob MarleyBob,
I don't know if column "i" is supposed to represent the PK or the combination of "i" and
"j". There isn't enough data here to make that determination, so I'll assume the latter:
select *
from B as b
where not exists (select * from A as a where a.i = b.i and a.j = b.j)
Hope this helps!
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Try:
select * from b
where not exists
(select * from a where a.i = b.i and a.j=b.j)
--
- Vishal
"Big Bob" <Bob@.hotmail.com> wrote in message news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Hi Bob
This is a perfect example of what correlated subqueries were invented for.
SELECT i,j
FROM B
WHERE NOT EXISTS (SELECT i, j FROM A
WHERE i = B.i AND j = B.j)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||Thanks Kalen!
Is there a way to do this with the new style SQL-92 joins rather than the
old SQL-89 join style. This is what I have been beating my head against the
wall with; the new join style.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> Hi Bob
> This is a perfect example of what correlated subqueries were invented for.
>
> SELECT i,j
> FROM B
> WHERE NOT EXISTS (SELECT i, j FROM A
> WHERE i = B.i AND j = B.j)
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > Consider the following schema:
> >
> > CREATE TABLE A(i int, j int)
> > CREATE TABLE B(i int, j int)
> >
> > INSERT A VALUES(1,1)
> > INSERT A VALUES(1,2)
> > INSERT A VALUES(2,1)
> > INSERT A VALUES(2,2)
> > INSERT A VALUES(2,3)
> >
> > INSERT B VALUES(3,1)
> > INSERT B VALUES(3,2)
> > INSERT B VALUES(1,1)
> > INSERT B VALUES(1,2)
> > INSERT B VALUES(2,2)
> > INSERT B VALUES(4,5)
> > INSERT B VALUES(5,4)
> >
> >
> > How do I compose a TSQL Query that will list the records in Table B,
that
> DO
> > NOT exist in Table A.?
> >
> > I would like a record set that looks like this:
> >
> > i j
> > -- --
> > 3 1
> > 3 2
> > 4 5
> > 5 4
> >
> >
> >
> > --
> > Bob Marley
> >
> >
>|||use NOT EXISTS
e.g.
select * from B
where NOT EXISTS
(select * from A where A.i = B.i and A.j = B.j)
"Big Bob" <Bob@.hotmail.com> wrote in message
news:%23Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> Consider the following schema:
> CREATE TABLE A(i int, j int)
> CREATE TABLE B(i int, j int)
> INSERT A VALUES(1,1)
> INSERT A VALUES(1,2)
> INSERT A VALUES(2,1)
> INSERT A VALUES(2,2)
> INSERT A VALUES(2,3)
> INSERT B VALUES(3,1)
> INSERT B VALUES(3,2)
> INSERT B VALUES(1,1)
> INSERT B VALUES(1,2)
> INSERT B VALUES(2,2)
> INSERT B VALUES(4,5)
> INSERT B VALUES(5,4)
>
> How do I compose a TSQL Query that will list the records in Table B, that
DO
> NOT exist in Table A.?
> I would like a record set that looks like this:
> i j
> -- --
> 3 1
> 3 2
> 4 5
> 5 4
>
> --
> Bob Marley
>|||I don't think this can be done with any kind of join.
If you have found a way to do it with the old style, post it and I'm sure
someone can easily convert it to new style.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||if you really want to use it, you can use a left join
select b.*
from b
left join a on b.i=a.i and b.j=a.j
where a.i is null
the where eliminates the joined rows, however
1. it defeats the purpose of a left join somewhat, which is to get all from
left, and matching from right
2. afaik, using exists is faster
"Big Bob" <Bob@.hotmail.com> wrote in message
news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> Thanks Kalen!
> Is there a way to do this with the new style SQL-92 joins rather than the
> old SQL-89 join style. This is what I have been beating my head against
the
> wall with; the new join style.
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > Hi Bob
> >
> > This is a perfect example of what correlated subqueries were invented
for.
> >
> >
> > SELECT i,j
> > FROM B
> > WHERE NOT EXISTS (SELECT i, j FROM A
> > WHERE i = B.i AND j = B.j)
> >
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Big Bob" <Bob@.hotmail.com> wrote in message
> > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > Consider the following schema:
> > >
> > > CREATE TABLE A(i int, j int)
> > > CREATE TABLE B(i int, j int)
> > >
> > > INSERT A VALUES(1,1)
> > > INSERT A VALUES(1,2)
> > > INSERT A VALUES(2,1)
> > > INSERT A VALUES(2,2)
> > > INSERT A VALUES(2,3)
> > >
> > > INSERT B VALUES(3,1)
> > > INSERT B VALUES(3,2)
> > > INSERT B VALUES(1,1)
> > > INSERT B VALUES(1,2)
> > > INSERT B VALUES(2,2)
> > > INSERT B VALUES(4,5)
> > > INSERT B VALUES(5,4)
> > >
> > >
> > > How do I compose a TSQL Query that will list the records in Table B,
> that
> > DO
> > > NOT exist in Table A.?
> > >
> > > I would like a record set that looks like this:
> > >
> > > i j
> > > -- --
> > > 3 1
> > > 3 2
> > > 4 5
> > > 5 4
> > >
> > >
> > >
> > > --
> > > Bob Marley
> > >
> > >
> >
> >
>|||Cool. Yes, it sort of defeats the purpose of LEFT JOIN, so maybe that's why
I didn't think of it. Also, it is prime example of why not all old style
joins can be converted easily to new style joins, or visa versa, as in this
case.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Trey Walpole" <treyNOpole@.SPcomcastAM.net> wrote in message
news:#Vf18JkrDHA.1692@.TK2MSFTNGP12.phx.gbl...
> if you really want to use it, you can use a left join
> select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
> the where eliminates the joined rows, however
> 1. it defeats the purpose of a left join somewhat, which is to get all
from
> left, and matching from right
> 2. afaik, using exists is faster
>
> "Big Bob" <Bob@.hotmail.com> wrote in message
> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
> > Thanks Kalen!
> >
> > Is there a way to do this with the new style SQL-92 joins rather than
the
> > old SQL-89 join style. This is what I have been beating my head against
> the
> > wall with; the new join style.
> >
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
> > > Hi Bob
> > >
> > > This is a perfect example of what correlated subqueries were invented
> for.
> > >
> > >
> > > SELECT i,j
> > > FROM B
> > > WHERE NOT EXISTS (SELECT i, j FROM A
> > > WHERE i = B.i AND j = B.j)
> > >
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Big Bob" <Bob@.hotmail.com> wrote in message
> > > news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
> > > > Consider the following schema:
> > > >
> > > > CREATE TABLE A(i int, j int)
> > > > CREATE TABLE B(i int, j int)
> > > >
> > > > INSERT A VALUES(1,1)
> > > > INSERT A VALUES(1,2)
> > > > INSERT A VALUES(2,1)
> > > > INSERT A VALUES(2,2)
> > > > INSERT A VALUES(2,3)
> > > >
> > > > INSERT B VALUES(3,1)
> > > > INSERT B VALUES(3,2)
> > > > INSERT B VALUES(1,1)
> > > > INSERT B VALUES(1,2)
> > > > INSERT B VALUES(2,2)
> > > > INSERT B VALUES(4,5)
> > > > INSERT B VALUES(5,4)
> > > >
> > > >
> > > > How do I compose a TSQL Query that will list the records in Table B,
> > that
> > > DO
> > > > NOT exist in Table A.?
> > > >
> > > > I would like a record set that looks like this:
> > > >
> > > > i j
> > > > -- --
> > > > 3 1
> > > > 3 2
> > > > 4 5
> > > > 5 4
> > > >
> > > >
> > > >
> > > > --
> > > > Bob Marley
> > > >
> > > >
> > >
> > >
> >
> >
>|||Here's a solution that uses an INNER JOIN and grouping:
select B.i, B.j
from B join A
on A.i < B.i and A.j < B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
And another, with T-SQL proprietary TOP .. WITH
TIES. It's not as efficient, but it's, well, it's another query...
select top 1 with ties B.i, B.j
from B join A
on A.i <> B.i or A.j <> B.j
group by B.i, B.j
order by count(*) desc
-- Steve Kass
-- Drew University
-- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
Trey Walpole wrote:
>if you really want to use it, you can use a left join
>select b.*
> from b
> left join a on b.i=a.i and b.j=a.j
> where a.i is null
>the where eliminates the joined rows, however
>1. it defeats the purpose of a left join somewhat, which is to get all from
>left, and matching from right
>2. afaik, using exists is faster
>
>"Big Bob" <Bob@.hotmail.com> wrote in message
>news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>
>>Thanks Kalen!
>>Is there a way to do this with the new style SQL-92 joins rather than the
>>old SQL-89 join style. This is what I have been beating my head against
>>
>the
>
>>wall with; the new join style.
>>
>>"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>>news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi Bob
>>This is a perfect example of what correlated subqueries were invented
>>
>for.
>
>>SELECT i,j
>>FROM B
>>WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>>--
>>HTH
>>--
>>Kalen Delaney
>>SQL Server MVP
>>www.SolidQualityLearning.com
>>
>>"Big Bob" <Bob@.hotmail.com> wrote in message
>>news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>>Consider the following schema:
>>CREATE TABLE A(i int, j int)
>>CREATE TABLE B(i int, j int)
>>INSERT A VALUES(1,1)
>>INSERT A VALUES(1,2)
>>INSERT A VALUES(2,1)
>>INSERT A VALUES(2,2)
>>INSERT A VALUES(2,3)
>>INSERT B VALUES(3,1)
>>INSERT B VALUES(3,2)
>>INSERT B VALUES(1,1)
>>INSERT B VALUES(1,2)
>>INSERT B VALUES(2,2)
>>INSERT B VALUES(4,5)
>>INSERT B VALUES(5,4)
>>
>>How do I compose a TSQL Query that will list the records in Table B,
>>
>>that
>>
>>DO
>>
>>NOT exist in Table A.?
>>I would like a record set that looks like this:
>>i j
>>-- --
>>3 1
>>3 2
>>4 5
>>5 4
>>
>>--
>>Bob Marley
>>
>>
>>
>>
>
>|||Oops - the first one should be
select B.i, B.j
from B join A
on A.i <= B.i and A.j <= B.j
group by B.i, B.j
having max(A.i) + max(A.j) < B.i + B.j
SK
Steve Kass wrote:
> Here's a solution that uses an INNER JOIN and grouping:
> select B.i, B.j
> from B join A
> on A.i < B.i and A.j < B.j
> group by B.i, B.j
> having max(A.i) + max(A.j) < B.i + B.j
>
> And another, with T-SQL proprietary TOP .. WITH
> TIES. It's not as efficient, but it's, well, it's another query...
> select top 1 with ties B.i, B.j
> from B join A
> on A.i <> B.i or A.j <> B.j
> group by B.i, B.j
> order by count(*) desc
>
> -- Steve Kass
> -- Drew University
> -- Ref: E3D3398B-1749-4F42-B5B0-6F7B71BA2AC5
> Trey Walpole wrote:
>> if you really want to use it, you can use a left join
>> select b.*
>> from b
>> left join a on b.i=a.i and b.j=a.j
>> where a.i is null
>> the where eliminates the joined rows, however
>> 1. it defeats the purpose of a left join somewhat, which is to get
>> all from
>> left, and matching from right
>> 2. afaik, using exists is faster
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:OVKp6xjrDHA.1364@.TK2MSFTNGP10.phx.gbl...
>>
>> Thanks Kalen!
>> Is there a way to do this with the new style SQL-92 joins rather
>> than the
>> old SQL-89 join style. This is what I have been beating my head
>> against
>>
>> the
>>
>> wall with; the new join style.
>>
>> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
>> news:Oc0QGvjrDHA.2248@.TK2MSFTNGP09.phx.gbl...
>>
>> Hi Bob
>> This is a perfect example of what correlated subqueries were invented
>>
>> for.
>>
>> SELECT i,j
>> FROM B
>> WHERE NOT EXISTS (SELECT i, j FROM A
>> WHERE i = B.i AND j = B.j)
>>
>> --
>> HTH
>> --
>> Kalen Delaney
>> SQL Server MVP
>> www.SolidQualityLearning.com
>>
>> "Big Bob" <Bob@.hotmail.com> wrote in message
>> news:#Kk3gnjrDHA.2400@.tk2msftngp13.phx.gbl...
>>
>> Consider the following schema:
>> CREATE TABLE A(i int, j int)
>> CREATE TABLE B(i int, j int)
>> INSERT A VALUES(1,1)
>> INSERT A VALUES(1,2)
>> INSERT A VALUES(2,1)
>> INSERT A VALUES(2,2)
>> INSERT A VALUES(2,3)
>> INSERT B VALUES(3,1)
>> INSERT B VALUES(3,2)
>> INSERT B VALUES(1,1)
>> INSERT B VALUES(1,2)
>> INSERT B VALUES(2,2)
>> INSERT B VALUES(4,5)
>> INSERT B VALUES(5,4)
>>
>> How do I compose a TSQL Query that will list the records in Table B,
>>
>> that
>>
>> DO
>>
>> NOT exist in Table A.?
>> I would like a record set that looks like this:
>> i j
>> -- --
>> 3 1
>> 3 2
>> 4 5
>> 5 4
>>
>> --
>> Bob Marley
>>
>>
>>
>>
>>
>>
>|||For those that are interested,Sql99 would allow this problem
to be easily solved without any correlated subquery,join or
grouping.Only a rank/counter based on the i,j combination and
table identification is necessary.Based on the rank, a simple
select would return the rows in table B not in A.
Here's the basic idea using the RAC utility to easily simulate
the sql99 rank function.
We create a union query where we assign a 1 (tableid) if the
i,j combination is from table A and a 2 if from table B.
We sort the data by i,j and tableid and obtain a rank
of each i,j combination.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
-- Rank i,j combinations based on the sort of i,j,tableid.
-- The rank column is named tablecnter.
@.rowcounters='j{tablecnter}'
i j tableid tablecnter
-- -- -- --
1 1 1 1
1 1 2 2
1 2 1 1
1 2 2 2
2 1 1 1
2 2 1 1
2 2 2 2
2 3 1 1
3 1 2 1
3 2 2 1
4 5 2 1
5 4 2 1
As you can see only when the tableid is 2 (table B) and
the rank (tablecnter) is 1 is it the case that the i,j
combination is in table B and not in table A.Now the
solution is simple.
Exec Rac
@.transform='_dummy_',
@.rows='i & j & tableid',
@.pvtcol='Sql*Plus',
@.from='(select i,j,2 as tableid from B
union all
select i,j,1 as tableid from A) as alldata',
@.grand_totals='n',@.rowbreak='n',
@.rowcounters='j{tablecnter}',
-- Check for tableid=2 and tablecnter=1 (data only in B).
@.select='select i,j
from rac
where tableid=2 and tablecnter=1
order by rd'
i j
-- --
3 1
3 2
4 5
5 4
RAC v2.2 and QALite @.
www.rac4sql.net