SQL filter all values in one column but not in another one

Question

I have created a query which is as follows:

SELECT t1.blah , t2.blah
FROM table1 AS t1
    INNER JOIN table2 AS t2 ON (t1.id = t2.id)

So the results looks like

t1.blah   t2.blah
=================
390       400
401       401
501       501
36        36

What I look for is to extract all values that are in t1.blah but never in t2.blah. In my example I should get as final result the value 390.

I tried to do some test with HAVING but I did not succeed. How can I achieve that in mysql for instance.

score 2 · Accepted Answer · answered Oct 12 '19 at 16:01

2

You can put one more condition on blah not matching in both the tables:

SELECT t1.blah , t2.blah
FROM table1 AS t1
    INNER JOIN table2 AS t2 
            ON t1.id = t2.id
               AND t1.blah <> t2.blah

answered Oct 12 '19 at 16:01

Madhur Bhaiya

28,155
10
49
57

@RaymondNijland Personally, I always prefer INNER JOIN over LEFT JOIN (as long as it can be done with INNER JOIN), as I have found it more performant. On a different topic, any experience with `JSON_ARRAYAGG()` function introduced in 5.7+ ? I am observing that MySQL does not use any index when using JSON_ARRAYAGG compared to creating JSON using GROUP_CONCAT and CONCAT functions. – Madhur Bhaiya Oct 12 '19 at 16:45
*"Personally, I always prefer INNER JOIN over LEFT JOIN (as long as it can be done with INNER JOIN), as I have found it more performant."* yes i removed mine comment but you totally are right.. LEFT JOIN would force MySQL into needed to fully table/index scan the left table before the LEFT JOIN clause.. For some reason it never popped into mine mind into using a exclusion inner join for missing values selection/checking how bizar is that.. – Raymond Nijland Oct 12 '19 at 16:48
*"On a different topic, any experience with JSON_ARRAYAGG() function introduced in 5.7+ ? I am observing that MySQL does not use any index when using JSON_ARRAYAGG compared to creating JSON using GROUP_CONCAT and CONCAT functions."* Make a question with examples on https://dba.stackexchange.com/ you know as good as i that is not really answerable here in the comments ;-) – Raymond Nijland Oct 12 '19 at 16:53
@RaymondNijland yeah I am just doing some testing on different patches of 5.7 before coming to a conclusion, and posting a question (I might even do a bug report at mysql website).. Will let you know once done. – Madhur Bhaiya Oct 12 '19 at 16:54
ok i could make a little snoop in the source code how `JSON_ARRAYAGG` is implemented, MySQL is known for adding new features "fast" and optimisation on those features might happen monthes, years after or not at all.. – Raymond Nijland Oct 12 '19 at 16:58
*" Will let you know once done. "* and whats the result what did you find out about the `JSON_ARRAYAGG` tending to not use indexes? – Raymond Nijland Oct 18 '19 at 10:11
@RaymondNijland stuck in some other optimization acitivities :-/ will try to post a question to dba.se by end of this week; will update you once done. – Madhur Bhaiya Oct 18 '19 at 10:23
@RaymondNijland maybe you can help here: https://stackoverflow.com/q/59499000/2469308 .. Regarding `JSON_ARRAYAGG` thing, I just could not find time to do more studies and prepare the question. If I come across it again, I will definitely do more testing then, and post it. – Madhur Bhaiya Dec 27 '19 at 10:15

SQL filter all values in one column but not in another one

1 Answers1