Query to obtain (A AND (NOT B))?

#1

Suppose I have two tables (A and B) with identical structures. I want
to obtain the recordset containing all records that ARE in A and are
NOT in B.

A = {1 2 3 4 5 6 7 8 9 10}
B = {2 4 6 8 10}

then

A & \B = {1 3 5 7 9}

Right?

Is this not a basic SQL query?

Is the syntax "A-B" or "A\B"?

#2

Prof Wonmug <wonmug@e.mcc> writes:

Quote:

Suppose I have two tables (A and B) with identical structures.

CREATE TABLE a (
foo INTEGER,
PRIMARY KEY (foo)
);
CREATE TABLE b (
foo INTEGER,
PRIMARY KEY (foo)
);

Quote:

I want to obtain the recordset containing all records that ARE in A
and are NOT in B.

A = {1 2 3 4 5 6 7 8 9 10}
B = {2 4 6 8 10}

INSERT INTO a (foo) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
INSERT INTO b (foo) VALUES
(2),
(4),
(6),
(8),
(10);

Quote:

then

A & \B = {1 3 5 7 9}

Right?

Is this not a basic SQL query?

You want the â€˜NOT INâ€™ operator of the â€˜WHEREâ€™ clause.

First, determine which values of â€˜fooâ€™ you *don't* want:

SELECT foo
FROM a
NATURAL JOIN b
;
foo
-----
2
4
6
8
10
(5 rows)

Then, select rows from â€˜aâ€™ where â€˜fooâ€™ is not in the unwanted value set:

SELECT foo
FROM a
WHERE foo NOT IN (
SELECT foo
FROM a
NATURAL JOIN b
)
;
foo
-----
1
3
5
7
9
(5 rows)

--
\ â€œI think it would be a good idea.â€ â€”Mohandas K. Gandhi (when |
`\ asked what he thought of Western civilization) |
_o__) |
Ben Finney

#3

On 17 Jan, 02:35, Prof Wonmug <won...@e.mcc> wrote:

Quote:

Suppose I have two tables (A and B) with identical structures. I want
to obtain the recordset containing all records that ARE in A and are
NOT in B.

* A = {1 2 3 4 5 6 7 8 9 10}
* B = {2 4 6 8 10}

then

* A & \B = {1 3 5 7 9}

Right?

Is this not a basic SQL query?

Is the syntax "A-B" or "A\B"?

That would have been nice. I never really understould why one should
have to type:

select * from T

instead of just

T

What you are looking for is something like:

select foo from A EXCEPT select foo from B

Some dbms use the keyword MINUS instead of EXCEPT. You can also use
predicates like:

not exists
not in

to accomplish the same thing.

/Lennart

#4

Lennart <erik.lennart.jonsson (AT) gmail (DOT) com> writes:

Quote:

I never really understould why one should have to type:

select * from T

instead of just

T

One defect among many in the design of SQL.

Fortunately, it's one that has been addressed in PostgreSQL at least:

The command

TABLE name

is completely equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax
variant in parts of complex queries.

<URL:http://www.postgresql.org/docs/8.4/static/sql-select.html#SQL-TABLE>

I don't have access to the SQL standard specification. Can someone show
an online reference for whether the â€˜TABLEâ€™ statement is standard?

--
\ â€œWithout cultural sanction, most or all of our religious |
`\ beliefs and rituals would fall into the domain of mental |
_o__) disturbance.â€ â€”John F. Schumaker |
Ben Finney

#5

Ben Finney wrote on 17.01.2010 07:01:

Quote:

Fortunately, it's one that has been addressed in PostgreSQL at least:

The command

TABLE name

is completely equivalent to

SELECT * FROM name

I don't have access to the SQL standard specification. Can someone show
an online reference for whether the â€˜TABLEâ€™ statement is standard?

I found the following in the SQL2003 foundation:

The <explicit table>

TABLE <table or query name>

is equivalent to the <query expression>
( SELECT * FROM <table or query name> )

So it seems that this is indeed part of the standard
(although Postgres seems to the only DBMS implementing that as far as I know)

Thomas

#6

On 17.01.2010 03:53, Ben Finney wrote:

Quote:

Prof Wonmug<wonmug@e.mcc> writes:

Suppose I have two tables (A and B) with identical structures.

CREATE TABLE a (
foo INTEGER,
PRIMARY KEY (foo)
);
CREATE TABLE b (
foo INTEGER,
PRIMARY KEY (foo)
);

I want to obtain the recordset containing all records that ARE in A
and are NOT in B.

A = {1 2 3 4 5 6 7 8 9 10}
B = {2 4 6 8 10}

INSERT INTO a (foo) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);
INSERT INTO b (foo) VALUES
(2),
(4),
(6),
(8),
(10);

then

A& \B = {1 3 5 7 9}

Right?

Is this not a basic SQL query?

You want the ‘NOT IN’ operator of the ‘WHERE’ clause.

First, determine which values of ‘foo’ you *don't* want:

SELECT foo
FROM a
NATURAL JOIN b
;
foo
-----
2
4
6
8
10
(5 rows)

Then, select rows from ‘a’ where ‘foo’ is not in the unwanted value set:

SELECT foo
FROM a
WHERE foo NOT IN (
SELECT foo
FROM a
NATURAL JOIN b
)
;
foo
-----
1
3
5
7
9
(5 rows)

I am not sure it is a good idea to always join - especially if A is
large. For me the most natural solution would involve NOT EXISTS:

SELECT foo
FROM A
WHERE NOT EXISTS (
SELECT foo
FROM B
WHERE B.foo = A.foo
)

Cheers

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

#7

On Jan 17, 11:16*am, Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com>
wrote:

Quote:

Ben Finney wrote on 17.01.2010 07:01:

Fortunately, it's one that has been addressed in PostgreSQL at least:

* * * The command

* * * TABLE name

* * * is completely equivalent to

* * * SELECT * FROM name

I don't have access to the SQL standard specification. Can someone show
an online reference for whether the ‘TABLE’ statement is standard?

I found the following in the SQL2003 foundation:

The <explicit table

TABLE <table or query name

is equivalent to the <query expression
( SELECT * FROM <table or query name> )

So it seems that this is indeed part of the standard
(although Postgres seems to the only DBMS implementing that as far as I know)

That's interesting and new to me. I checked with Mimers sql-validator
and it confirms it:

http://developer.mimer.com/validator...dex.tml#parser

Result:
The following feature outside Core SQL-99 is used:

F661, "Simple tables"

I wonder what the odds are that we will get the possibility to specify
a subset of columns as in:

table t(c_1, c_3, c_n)

and even

table t(c_1, c_3, c_n) extend (...)

/Lennart

#8

Quote:

select foo from A EXCEPT select foo from B

EXCEPT: that's neat, like the opposite of UNION.

Quote:

Some dbms use the keyword MINUS instead of EXCEPT. You can also use
predicates like:

not exists
not in

you can also use a join.

select a.foo from a left outer join b on a.foo=b.foo where b.foo is null;

Jasen.

--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

#9

Lennart <erik.lennart.jonsson (AT) gmail (DOT) com> wrote in
news:55745f1a-7d84-4665-ac6a-1677d86d980e (AT) m16g2000yqc (DOT) googlegroups.com:

Quote:

On Jan 17, 11:16*am, Thomas Kellerer <OTPXDAJCS... (AT) spammotel (DOT) com
wrote:
Ben Finney wrote on 17.01.2010 07:01:

Fortunately, it's one that has been addressed in PostgreSQL at
least:

* * * The command

* * * TABLE name

* * * is completely equivalent to

* * * SELECT * FROM name

I don't have access to the SQL standard specification. Can someone
show an online reference for whether the ‘TABLE’ statement is
standard?

I found the following in the SQL2003 foundation:

The <explicit table

TABLE <table or query name

is equivalent to the <query expression
( SELECT * FROM <table or query name> )

So it seems that this is indeed part of the standard
(although Postgres seems to the only DBMS implementing that as far as
I k
now)

That's interesting and new to me. I checked with Mimers sql-validator
and it confirms it:

http://developer.mimer.com/validator...dex.tml#parser

Result:
The following feature outside Core SQL-99 is used:

F661, "Simple tables"

The way to write a join without starting with the SELECT keyword has been
removed from the SQL standard (SQL-2003 and later).

/Jarl

#10

Oops... Now I see what you mean. (Too bad I don't read carefully enough
before I post.)

Yes, "TABLE table-name" is valid SQL syntax. (Feature 661, Simple tables).

/Jarl