How to combine the queries without any extra lines?

Have a table of data and actives
data:

| id | name | desc |
| 3 | fdd | fsdfsdf |
| 2 | fdd | fsdfsdf |
| 5 | fdd | fsdfsdf |

actives:

| id | data_id | test |
| 1 | 3 | 123 |
| 2 | 2 | 112312 |

It is necessary to:

| id | data_id | name | desc |
| 1 | 5 | fdd | fsdfsdf |


It is necessary to obtain from the data only those elements, which is not in the table actives
Tried using JOIN, but when I put condition != too much unnecessary data
April 4th 20 at 12:56
2 answers
April 4th 20 at 12:58
Solution
It is not necessary to use not in where not exists is needed.
select * from data where not exists (select from actives where data_id = data.id);


Due to the requirements of the standard as part of the treatment of NULL in not in and because of the lack of specific optimization for these not in they are very different in performance.
Correct: select * from data where not exists (select * from actives where data_id = data.id); - ludwig commented on April 4th 20 at 13:01
@ludwig, danke, John DOE
But select from - not a bug, it's a valid entry. Afaik, 9.4+. To read field request is not necessary, all the more so. - oliver_Spence commented on April 4th 20 at 13:04
@oliver_Spence, I think that still not everyone uses MySQL 9.4+ and, for example, in MySQL 5.7.28 this entry will not be valid without an asterisk. With the asterisk solution will be more universal, backwards compatible. And about:

It is not necessary to use not in where not exists is needed.
Due to the requirements of the standard as part of the treatment of NULL in not in and because of the lack of specific optimization for these not in they are very different in performance.


In my opinion, is when you create the fields in the table add Not NULL condition to be sure that NULL will Not EXIST and then it will work the same way with Not In. Although, of course, Not EXIST it will be faster than Not IN, even if in columns, and not NULL, but it is allowed. - ludwig commented on April 4th 20 at 13:07
@ludwig, where did you get mysql?
The question, accordingly, my answer relate to postgresql. A branch 9.4 3 weeks already EOL.

Standard:
select * from data where not exists (select NULL from actives where actives.data_id = data.id);


not null for fields where null should not be, of course, mandatory. The question does not predusmotreno optimization not in is not the case. There are no optimizations and the scheduler will not make antijoin

Well, for old mysql, for example, it may be mentioned that there was a bug scheduler to emnip 5.5 and write this not exists was better than not using left join. - oliver_Spence commented on April 4th 20 at 13:10
@oliver_Spence,

and where did you get mysql?

Sorry, forgot about the tags issue. - ludwig commented on April 4th 20 at 13:13
April 4th 20 at 13:00
Solution
select * from data where id not in (select id from actives)
Wrong, your query will return two rows:

3 fdd fsdfsdf
5 fdd fsdfsdf

And you need one:

5 fdd fsdfsdf

Ie: select * from data where id not in (select data_id from actives); - ludwig commented on April 4th 20 at 13:03
Yes. May be so. A General framework I wrote. The field already the author assumes. - claire36 commented on April 4th 20 at 13:06

Find more questions by tags PostgreSQLSQL