ansaurus

Question

Having trouble with this simple SQL Select statement.

Answer 1

+6 A:

assuming TableA and TableB have a primary key of name id.

select TableA.* 
  from TableA 
left outer join TableB on TableB.id = TableA.id
where TableB.id is null;

This will find all entries where table b does not have an instance of table a's id.

joeslice 2009-09-16 01:52:23

I get an error using TableA.*, I'm SQL Server Express 2008. What can I do?

Sergio Tapia 2009-09-16 01:54:27

You could enumerate the columns you want. For example select TableA.id, TableA.otherColumn, etc.

joeslice 2009-09-16 01:55:28

Fixed it using this: select * from ProductoA LEFT OUTER JOIN ProductoB on ProductoB.Descripcion = ProductoA.Descripcionwhere ProductoB.Descripcion is null;

Sergio Tapia 2009-09-16 01:56:50

You're making the assumption here that all products have a unique description. Note that if you have two products in TableB that have the same description or the description in TableA matches a description in TableB for which the rest of the data does not match you will select incorrect data.

tvanfosson 2009-09-16 02:00:38

In SQL Server, NOT IN / NOT EXISTS are better than LEFT JOIN, since its optimizer can't discern an ANTI JOIN in a LEFT JOIN / IS NULL. It will always return all NULLS first and filter them out.

OMG Ponies 2009-09-16 02:07:03

Answer 2

+2 A:

You have your terminology wrong. The intersection would be the rows that are in both Table A and Table B. What you are actually looking for is the relative complement of A and B. To get a relative complement you want to do an antijoin:

SELECT * FROM TableA EXCEPT SELECT * FROM TableB.

tvanfosson 2009-09-16 01:52:55

Thank you very much, this is really informative stuff! One question though, you antijoin statement is based on the PrimaryKeys of the tables correct? What I mean is, they automatically use the PrimaryKeys to do their stuff, right?

Sergio Tapia 2009-09-16 02:03:42

No -- it's comparing all of the columns. It will only filter out complete rows that exist in Table A that are also in Table B.

tvanfosson 2009-09-16 02:05:25

Answer 3

+2 A:

SELECT ColumnA, ColumnB
FROM TableA
EXCEPT
SELECT ColumnA, ColumnB
FROM TableB

Pierre-Alain Vigeant 2009-09-16 01:53:09

Answer 4

+4 A:

You could use the EXISTS clause

SELECT * FROM TableA
WHERE NOT Exists
(
SELECT Column1 FROM TableB 
WHERE TableA.Column1 = Table2.Column1 
AND TableA.Column2 = Table2.Column2 
....
)

Replace .... with the rest of the columns in the two tables.

Kibbee 2009-09-16 01:53:12

This is the better answer. In SQL Server, NOT IN / NOT EXISTS are better than LEFT JOIN, since its optimizer can't discern an ANTI JOIN in a LEFT JOIN / IS NULL. It will always return all NULLS first and filter them out.

OMG Ponies 2009-09-16 02:05:00

Answer 5

+1 A:

or NOT IN

SELECT *
FROM TableA
WHERE TableA.Id NOT IN (SELECT TableB.Id FROM TableB)

Rafe Lavelle 2009-09-16 02:00:38

ansaurus

tags:

views:

answers:

Having trouble with this simple SQL Select statement.

related questions