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

#11

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

Quote:

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

The big gain with the TABLE statement is not having to specify a SELECT
when one doesn't want to select (i.e. when one wants all the fields
instead of a selection of them).

Quote:

as in:

table t(c_1, c_3, c_n)

The use case for that seems much less appealing. There's very little
gained by the above compared to:

SELECT c_1, c_3, c_n FROM t

Quote:

and even

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

Well, the easy answer to that is: I think other languages will provide
Tutorial D capabilities long before the SQL standard mutates into
Tutorial D.

--
\ â€œI have a large seashell collection, which I keep scattered on |
`\ the beaches all over the world. Maybe you've seen it.â€ â€”Steven |
_o__) Wright |
Ben Finney

#12

On Sun, 17 Jan 2010 21:15:47 +0100, Robert Klemme
<shortcutter (AT) googlemail (DOT) com> wrote:

Quote:

On 17.01.2010 03:53, Ben Finney wrote:
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:

In my application, A can grow to at least 1E5 records, but probably
will not exceed 1E6 records. B will rarely exceed 1E4 records and will
always be a proper subset of A.

Quote:

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

Are all of these commands supported by MySQL?

#13

On 18 Jan 2010 09:29:01 GMT, Jasen Betts <jasen (AT) xnet (DOT) co.nz> wrote:

Quote:

select foo from A EXCEPT select foo from B

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

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;

Are all of these commands supported in mySQL?

#14

Prof Wonmug <wonmug@e.mcc> writes:

Quote:

Are all of these commands supported by MySQL?

Prof Wonmug <wonmug@e.mcc> writes:

Quote:

Are all of these commands supported in mySQL?

What happens when you try it on your installation of MySQL?

--
\ â€œFacts are meaningless. You could use facts to prove anything |
`\ that's even remotely true!â€ â€”Homer, _The Simpsons_ |
_o__) |
Ben Finney

#15

On Tue, 19 Jan 2010 08:37:02 +1100, Ben Finney
<bignose+hates-spam (AT) benfinney (DOT) id.au> wrote:

Quote:

Prof Wonmug <wonmug@e.mcc> writes:

Are all of these commands supported by MySQL?

Prof Wonmug <wonmug@e.mcc> writes:

Are all of these commands supported in mySQL?

What happens when you try it on your installation of MySQL?

I don't have an installation of MySQL or anything else (except
Access). I am trying to determine which database system to use. I was
hoping that a MySQL expert could tell me.

#16

On 19 Jan., 05:43, Prof Wonmug <won...@e.mcc> wrote:

Quote:

On Tue, 19 Jan 2010 08:37:02 +1100, Ben Finney

bignose+hates-s... (AT) benfinney (DOT) id.au> wrote:
Prof Wonmug <won...@e.mcc> writes:

Are all of these commands supported by MySQL?

Prof Wonmug <won...@e.mcc> writes:

Are all of these commands supported in mySQL?

What happens when you try it on your installation of MySQL?

I don't have an installation of MySQL or anything else (except
Access). I am trying to determine which database system to use. I was
hoping that a MySQL expert could tell me.

Reading documentation is not an option?
http://dev.mysql.com/doc/refman/5.5/en/select.html

#17

On 2010-01-18, Prof Wonmug <wonmug@e.mcc> wrote:

Quote:

On 18 Jan 2010 09:29:01 GMT, Jasen Betts <jasen (AT) xnet (DOT) co.nz> wrote:

select foo from A EXCEPT select foo from B

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

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;

Are all of these commands supported in mySQL?

dunno, but postgresql supports them all.

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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

comp.databases comp.databases

Re: TABLE statement - 01-18-2010 , 06:52 AM

Re: Query to obtain (A AND (NOT B))? - 01-18-2010 , 10:38 AM

Re: Query to obtain (A AND (NOT B))? - 01-18-2010 , 10:39 AM

Re: Query to obtain (A AND (NOT B))? - 01-18-2010 , 04:37 PM

Re: Query to obtain (A AND (NOT B))? - 01-18-2010 , 11:43 PM

Re: Query to obtain (A AND (NOT B))? - 01-19-2010 , 05:41 AM

Re: Query to obtain (A AND (NOT B))? - 01-20-2010 , 07:53 AM