[SQL snippet] Select from tableOne if not in tableTwo

Magentix

if (OP.statement == false) postCount++;
Reaction score
107
I just had a hard time figuring this SQL commando out and it might prove useful for others that encounter the problem in the future...

What it does:
Take unique values from fieldName of tableOne that don't exist in fieldName of tableTwo

Sounds simple eh? Well check this out:
Code:
SELECT tableUnion.fieldName AS unionFieldName FROM(
	SELECT DISTINCT fieldName FROM tableOne
	UNION ALL
	SELECT y.fieldName FROM tableTwo AS y
	INNER JOIN tableOne AS x
	ON y.fieldName =x.fieldName 
) AS tableUnion
GROUP BY unionFieldName 
HAVING ( COUNT(unionFieldName )=1 )

If you can use it: enjoy!
Otherwise: Don't be mad that I just stole 20 seconds of your life...
 

Magentix

if (OP.statement == false) postCount++;
Reaction score
107
Code:
SELECT DISTINCT fieldName FROM tableOne WHERE fieldName NOT IN (SELECT fieldName FROM tableTwo);
Looks more natural to me.

The f...?

I tried that earlier and got a syntax error, now that I try that again, it works...
*sigh*
 

phyrex1an

Staff Member and irregular helper
Reaction score
446
That JOIN will really make that slow... @ OP
You're saying that in a way that makes it sound like you think joins in general are slow. Not the case, in fact "my" query also contains a join but a much faster one.

(index on both user_name and page_title, they are both varchar fields)

Code:
DESCRIBE SELECT DISTINCT user_name FROM w_user WHERE user_name NOT IN (SELECT page_title FROM w_page);
Code:
'id', 'select_type', 'table', 'type', 'possible_keys', 'key', 'key_len', 'ref', 'rows', 'Extra'
1, 'PRIMARY', 'w_user', 'index', , 'user_name', '257', , 67, 'Using where; Using index'
2, 'DEPENDENT SUBQUERY', 'w_page', 'index', , 'name_title', '771', , 23778, 'Using where; Using index'


Code:
EXPLAIN SELECT tableUnion.fieldName AS unionFieldName FROM(
	SELECT DISTINCT user_name as fieldName FROM w_user
	UNION ALL
	SELECT y.page_title as fieldName FROM w_page AS y
	INNER JOIN w_user AS x
	ON y.page_title =x.user_name 
) AS tableUnion
GROUP BY unionFieldName 
HAVING ( COUNT(unionFieldName )=1 )
Code:
'id', 'select_type', 'table', 'type', 'possible_keys', 'key', 'key_len', 'ref', 'rows', 'Extra'
1, 'PRIMARY', '<derived2>', 'ALL', , , , , 94, 'Using temporary; Using filesort'
2, 'DERIVED', 'w_user', 'index', , 'user_name', '257', , 67, 'Using index'
3, 'UNION', 'x', 'index', , 'user_name', '257', , 67, 'Using index'
3, 'UNION', 'y', 'index', , 'name_title', '771', , 23778, 'Using where; Using index; Using join buffer'
, 'UNION RESULT', '<union2,3>', 'ALL', , , , , , ''

I suspect that the slowest thing in this query is the temporary and filesort resulting from the group by and having. Then there is the join buffer that results from the inner join in the union.
 
General chit-chat
Help Users
  • No one is chatting at the moment.

      The Helper Discord

      Members online

      No members online now.

      Affiliates

      Hive Workshop NUON Dome World Editor Tutorials

      Network Sponsors

      Apex Steel Pipe - Buys and sells Steel Pipe.
      Top