Mysql – Query difference with where condition

MySQLquery-refactor

I need to find the ids of all rows in a table A without a matching row on table B. Following the answer on this question I'm using a left join like this:

select A.id from A left join B on B.id_A = A.id where B.id_A is null;

And it works perfectly well. However now I need to do the same query with an additional conditional for the matching, meaning I need to find the ids of all rows in a table A without a matching row on table B whose column c has the value x. If I try something like:

select A.id from A left join B on B.id_A = A.id where B.id_A is null and B.c = x;

It obviously gives me an empty result set.

So far, the only way I figured to do that is using a subquery for B with a "not exists" clause:

select A.id from A where not exists (select id from B where B.id_A = A.id and B.c = x);

Any ideas on how to do this with a join without using subqueries?

Best Answer

SELECT A.id 
  FROM A 
  LEFT JOIN B ON B.id_A = A.id AND B.c = x 
 WHERE B.id_A is null;

Moving the test of B.c into the join condition and out of the where clause causes it to be used to only eliminate non-matching B-rows from consideration for the left join rather than eliminating rows from the result.

Related Solutions

MySQL – Get Column from Multiple Tables

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

SELECT A.*
FROM default_relations_users A
LEFT JOIN default_relations_users B
ON  A.id_user_rq = B.id_user_ap
AND A.id_user_ap = B.id_user_rq
WHERE B.id_user_rq IS NULL;

I loaded you sample data (I added an additional index)

mysql> use Reynierpm
Database changed
mysql> DROP TABLE IF EXISTS `default_relations_users`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `default_relations_users` (
    ->   `id_user_rq` int(11) NOT NULL,
    ->   `id_user_ap` int(11) NOT NULL,
    ->   UNIQUE KEY `rusers_rq_ap_idx` (`id_user_rq`,`id_user_ap`),
    ->   UNIQUE KEY `rusers_ap_eq_idx` (`id_user_ap`,`id_user_rq`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `default_relations_users` (`id_user_rq`, `id_user_ap`) VALUES
    -> (1, 2), (1, 3), (1, 4), (2, 1), (2, 2), (2, 3);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from default_relations_users;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          2 |
|          1 |          3 |
|          1 |          4 |
|          2 |          1 |
|          2 |          2 |
|          2 |          3 |
+------------+------------+
6 rows in set (0.00 sec)

mysql>

Here is the result of my answer

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
|          2 |          3 |
+------------+------------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!

mysql> SELECT A.*
    -> FROM default_relations_users A
    -> LEFT JOIN default_relations_users B
    -> ON  A.id_user_rq = B.id_user_ap
    -> AND A.id_user_ap = B.id_user_rq
    -> WHERE B.id_user_rq IS NULL
    -> AND A.id_user_rq = 1;
+------------+------------+
| id_user_rq | id_user_ap |
+------------+------------+
|          1 |          3 |
|          1 |          4 |
+------------+------------+
2 rows in set (0.01 sec)

mysql>

Best Answer

Related Solutions

MySQL – Get Column from Multiple Tables

Mysql – How to select/count rows where the same value exists in a column but not in other column all at the same table

Related Question