Member Avatar for michael123

In database I have three tables: t1,t2,t3
all three tables contain the same field (username)
t1(username)
-------
user1
user2
user3
user4
user5

t2(username)
-------
user3
user4

t3(username)
-------
user2


Now how can I write SELECT statment to retrieve all username from table1(t1) which is NOT included in table2(t2) and table3(t3)? thanks.

  • 3 Contributors
  • 8 Replies
  • 221 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Kate Albany

Dani AI

Generated

Problem: return rows from t1 (or from the example tables authuser) that do not appear in t2/t3 (or memberhist). ’s NOT IN/subquery suggestion is the usual, but is on MySQL 3.23 which does not have sub-selects. The later attempt using inequality joins produced a partial Cartesian product — exactly the issue described — so an anti-join is required instead.

Use LEFT JOINs and filter for NULL on the joined tables (no subquery needed):

SELECT a.uname
FROM authuser AS a
LEFT JOIN memberhist AS b ON a.uname = b.uname
WHERE b.uname IS NULL;

For the original three-table case the same pattern applies:

SELECT a.username
FROM t1 AS a
LEFT JOIN t2 AS b ON a.username = b.username
LEFT JOIN t3 AS c ON a.username = c.username
WHERE b.username IS NULL
  AND c.username IS NULL;

Why this works: LEFT JOIN preserves each row from the left table once; when no match exists the joined columns are NULL, so selecting rows where the joined table columns are NULL yields the “not in” set without generating the many extra rows produced by inequality joins. DISTINCT only masks the symptom and won’t reliably solve the Cartesian-pair explosion.

Cautions and tips: ensure username is NOT NULL (or handle NULLs with COALESCE) because NULLs affect equality and NOT IN logic; add indexes on the joined columns for performance; enforce uniqueness on username if duplicates are not desired (or use DISTINCT if duplicates must be collapsed). If the server is later upgraded, the equivalent NOT EXISTS / NOT IN subqueries are clearer alternatives.

Recommended Answers

All 8 Replies

Member Avatar for campkev

select username from t1 where username not in (select username from t2) and username not in (select username from t3)

Member Avatar for michael123

I use Mysql 3.23 server, I think it doesn't support sub-select statement, is there alternative to do that? thanks.

Member Avatar for campkev

upgrade to 4.1?

Member Avatar for michael123

I use hosting services, so it's impossible for me to upgrade it, I am just wondering if there a way to do it, except using sub-select statement.

Member Avatar for campkev

can you do joins?

select * from t1
inner join t2 on t1.userid != t2.userid
inner join t3 on t1.userid !- t3.userid

Member Avatar for michael123

still have problem.
For example:

mysql> select uname from authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows in set (0.00 sec)

mysql> select uname from memberhist;
+-------+
| uname |
+-------+
| user1 |
+-------+
1 row in set (0.00 sec)

mysql> select distinct a.uname from authuser a,memberhist b
    -> where a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
+--------+
3 rows in set (0.00 sec)

If table "memberhist" has only one record, then I can get correct result(find users which in table "authuser" but not in "memberhist"), however if I add a new entry to "memberhist", then the result is wrong, see below:

mysql> select uname from authuser;
+--------+
| uname  |
+--------+
| user1  |
| user12 |
| user2  |
| user3  |
+--------+
4 rows in set (0.00 sec)


mysql> select uname from memberhist;
+-------+
| uname |
+-------+
| user1 |
| user3 |
+-------+
2 rows in set (0.01 sec)


mysql> select distinct a.uname from authuser a,memberhist b
    -> where a.uname<>b.uname;
+--------+
| uname  |
+--------+
| user12 |
| user2  |
| user3  |
| user1  |
+--------+
4 rows in set (0.00 sec)

Suppose to get the result of "user12,user2", but it shows "user12,user2,user3,user1".

Don't know how to fix it.

Member Avatar for Kate Albany

Hello,

The reason that your query does not work correctly is because by using <> in this manner you are creating a 'partial' Cartesian Join between the two tables. In doing so the query returns you a 'partial' Cartesian Product. Though this fact is masked from you in your first example by the use of 'distinct'. Run your query using 'select *' and you will see what is really happening. Google for Cartesian Join/Cartesian Product to find out more.

The example below is in Oracle syntax. So I suspect you will have to translate the (+) syntax for use with MySQL. It is an inner or outer join, I can never remember which. I just know which side of the join to place the (+) when needed :)

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

SQL> set feedback on;
SQL> select * from users1;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> select * from users2;

USERNAME
--------------------------------------------------
user3
user4
user6

3 rows selected.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

SQL> delete from users2 u2 where u2.username = 'user3';

1 row deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user5

4 rows selected.

SQL> delete from users2;

2 rows deleted.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user3
user4
user5
user6

6 rows selected.

SQL> rollback;

Rollback complete.

SQL> select   distinct(u1.username)
  2  from	 users1 u1,
  3		   users2 u2
  4  where	u1.username = u2.username(+)
  5  and	  u2.username is null;

USERNAME
--------------------------------------------------
user1
user2
user5

3 rows selected.

Hope that helps

Kate

Member Avatar for Kate Albany

It is an ugly solution I would not personally use in Oracle, though as you say you cannot use sub-querys, I know of on better method.

Also, in my example you do not need to use the 'distinct' keyword, but it may save you problems down the line should you ever get duplicate records in the table. Though a good index enforcing a unique constraint would stop that altheother ;)

Kate

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.