Sets operations (n,?,¬) and SQL 2

[Rydel]

I am sure it must be something rather simple, but I just can't think of it right now. I have two tables with usernames: t1 and t2. And I would like to be able to perform set operations on them:

1) t = t1 ? t2 (SELECT all those that are either in t1 or in t2, removing dupicates. Somehow I have difficulty with UNION query in MS Access, described in another tek-tip's thread)

2) t = t1 n t2 (SELECT all those that are both in t1 and t2. I already did this one. "SELECT t1.username FROM t1, t2 WHERE t1.username=t2.username")

3) t = t1 n ¬t2 (SELECT all those that are in t1 but NOT in t2. This seems to be tricky. I am not sure how to do it.)

Big thanks in advance!

regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[rac2]

In SQL, UNION eliminates duplicates, UNION ALL includes duplicates as many times as they occur.

SELECT a,b,c FROM tblOne
UNION
SELECT m,k,h FROM tblTwo

The t = t1 n ¬t2 can be obtained in SQL using a subquery.

SELECT a,b,c FROM tblOne
WHERE a NOT IN ( SELECT m FROM tblTwo )

Here column a in tblOne is a foreign key linking to column m in tblTwo.

 

[PHV]

1)
SELECT DISTINCT U.username
FROM (SELECT username FROM t1
UNION SELECT username FROM t2) AS U;
2)
SELECT t1.username
FROM t1 INNER JOIN t2 ON t1.username=t2.username;
3)
SELECT username FROM t1
WHERE username NOT IN (SELECT username FROM t2);
or
SELECT t1.username
FROM t1 LEFT JOIN t2 ON t1.username=t2.username
WHERE t2.username IS NULL;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DanJR]

Firstly, SQL usually produces multiset results except for set operations eg UNION where the resuls are relations. The reason is that creating relations is expensive. In order to produce a relation you can use DISTINCT e.g.

SELECT DISTINCT P.username
  FROM people P;

Actually, assuming the username attribute is unique, then we don't need DISTINCT for these queries.

For set-union use the UNION clause (you dont need DISTINCT because by default duplicates are removed ie the result is a relation:

SELECT P.username
  FROM people1 P
UNION
SELECT A.username
  FROM alien A

For set-intersection, MS Access implementation of SQL doesnt support the INTERSECT clause. For example,
ANSI SQL:

SELECT P.username
  FROM people1 P
INTERSECT
SELECT A.username
  FROM alien A

But in MS Access, one possible solution is:

SELECT P.username
  FROM people P
  WHERE EXISTS (SELECT A.username
                  FROM alien A
                  WHERE A.username=P.username);

For set-difference, again MS Access does not support the EXCEPT clause (or in Oracle the MINUS clause). For example,
ANSI SQL:

SELECT P.username
  FROM people1 P
EXCEPT
SELECT A.username
  FROM alien A

But in MS Access, one possible solution:

SELECT P.username
  FROM people p
  WHERE NOT EXISTS (SELECT A.username
                      FROM alien A
                      WHERE A.username=T.username);

In MS Access, to do set-intersect and set-difference will require a nested query of some sort. Of course there are other ways, e.g. You could use the NOT clause instead of the EXISTS clause.

eg. using NOT IN for set-difference

SELECT T.Username
  FROM Table1 AS T
  WHERE T.username 
          NOT IN  (SELECT T2.username
                     FROM Table2 T2
                     WHERE T2.username=T.username);

There are plenty of books that discribe how relational algebra can be implemented with ANSI SQL (e.g. Database Management Systems, 3rd Edition, Ramakrishnan and Gehrke, 2003). After understanding ANSI SQL, you just need to find out how its implemented in each product. Most products are based on ANSI-SQL '92.

Cheers,
Dan

 

[Rydel]

PHV, thank you very much! (3) worked like a charm!
But there is still a problem with the UNION. Do you think there is a way, let's say, with JOIN's?



regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[Rydel]

DanJR, thanks for this minilecture. One additional question: can you make a union without using a UNION query, that is, for example, using JOIN's?


regards,
rydel n23

http://pravapis.org/

http://rydel.net/

 

[DanJR]

No sorry, not that I know about.

If your union sql is causing problems then paste the query so everyone can see - and hopefully someone will provide a solution

cheers,
Dan

PS, I stand corrected. I said: "In MS Access, to do set-intersect and set-difference will require a nested query of some sort" - is isn't true as PHV showed examples of using a JOIN (thanks PHV).

 

[Rydel]

PHV helped me to solve the UNION mystery, which was nothing more but my one stupidity (leading spaces):

http://www.tek-tips.com/viewthread.cfm?SQID=854724&SPID=701&page=1

regards,
rydel n23

http://pravapis.org/

http://rydel.net/