Tuesday, July 20, 2010

SQL = Sets Quarantined, Lost

I have two tables in a MySQL database. They both have persons, but BOTH tables have persons that are not in both. So, I wanted to bring all those people that are not repeated. I can do this using inner loops, but I tried to do it by MySQL alone.

I elaborated an example with two sets: t1=[a,b,c]; t2=[a,b,d]. What I want to bring is this set: r=[c,d]; because [a,b] can be retrieved by a simple INNER JOIN.


#TEMPORARY TABLES WE WILL USE
#TABLES WITH ELEMENTS WILL BE: t1 AND t2

DROP TEMPORARY TABLE IF EXISTS t1,t2,plus,minus;

#FIRST DATA SET
CREATE TEMPORARY TABLE t1 AS
SELECT 'a' AS f1 UNION SELECT 'b' UNION SELECT 'c';

#SECOND DATA SET
CREATE TEMPORARY TABLE t2 AS
SELECT 'a' AS f2 UNION SELECT 'b' UNION SELECT 'd';

#CREATING THE "UNION"
CREATE TEMPORARY TABLE plus AS
SELECT * FROM t1
UNION
SELECT * FROM t2;

#CREATING THE "INTERSECTION"
CREATE TEMPORARY TABLE minus AS
SELECT t1.* FROM t1
INNER JOIN t2
ON t1.f1=t2.f2;

#CHECK OUR TEMPORARY TABLES
SELECT * FROM t1; #FIRST TABLE
SELECT * FROM t2; #SECOND TABLE
SELECT * FROM plus; #ALL ELEMENTS
SELECT * FROM minus; #COMMON ELEMENTS

#AND THIS IS THE OPERATOR I NEED IN MYSQL
SELECT plus.* FROM plus
LEFT JOIN minus
ON minus.f1=plus.f1
WHERE minus.f1 IS NULL

/*
AN UNION MINUS AN INTERSECTION BRINGS ALL RECORDS
THAT ARE NOT PRESENT IN ANY SET

YOU SEE, VENN DIAGRAMS AND SET THEORY ARE TOO OLD SCHOOL
THAT THEY SEEM NOT NEEDED NOWADAYS. BUT NOW,
OBJECT-ORIENTED ANALYSIS AND DESIGN IS, AGAIN,
BRINGING ALL THOSE OLD-SCHOOLERS BACK!!
*/

No comments:

Post a Comment