🚀 go-pugleaf

On 8 Jun 1998 05:47:34 GMT, "Tom Gleaton" <tgleaton@linkline.com> wrote:

>Table A & B each have a Customer No field.
>How would I write an SQL statement to select all rows in table A,
>that do not have a Customer Number in table B?

There are a number of ways to do that. One uses the IN predicate, negated
with the NOT logical operator.

  SELECT A."Customer No"
  FROM A
  WHERE (A."Customer No" NOT IN
    (SELECT DISTINCT B."Customer No"
    FROM B)
    )

Another way uses the EXISTS predicate, again negated by the NOT operator.

  SELECT A."Customer No"
  FROM A
  WHERE (NOT EXISTS
    (SELECT DISTINCT B."Customer No"
    FROM B
    WHERE (B."Customer No" = A."Customer No"))
    )

And still another way involves joining the tables in an outer join and then
filtering the data to just those joined rows where the B table key column
is empty (contains a NULL value), something only possible when a given row
in the A table does not have a match in the B table.

  SELECT DISTINCT A."Customer No"
  FROM A
    LEFT OUTER JOIN B
      ON (A."Customer No" = B."Customer No")
  WHERE (B."Customer No" IS NULL)

//////////////////////////////////////////////////////////////////////////
Steve Koterski                      "Knowledge comes, but wisdom lingers."
Technical Publications                -- Alfred, Lord Tennyson (1809-1914)
INPRISE Corporation
http://www.inprise.com/delphi