mysql - sql 根据明细表一个字段查询主表列表，使用group,disctinct还是exists

Question

有两个表：
A：

| id | name | ---id主键

B:

| id | a_id | item_id | item_name | desc | ----id主键，a_id 与 A中的id关联

查询-包含item_id的所有的A记录

SELECT DISTINCT A.* FROM A JOIN B ON A.id = B.a_id WHERE item_id = 123;

SELECT A.* FROM A JOIN B ON A.id = B.a_id WHERE B.item_id =123 GROUP BY A.id;

SELECT A.* FROM A WHERE EXISTS (SELECT 1 FROM B WHERE B.a_id = A.id and B.item_id = 123);

这三种查询那种最好？

Answer 1

If the amount of data is large, it is not recommended that you use the second or third method. It is recommended that you use the first one. When we were working on a project, every time the DBA reviewed our SQL, whenever a subquery appeared or GROUP BY was used in the SQL, he would ask us about the size of the data and how the table was built, and would advise us not to use GROUP BY and subqueries. They always say the efficiency is slow... I'm a newbie and I don't know exactly why it's slow

Answer 2

It is recommended to use the third option, which best meets your requirements semantically and should also be the most efficient.
The first way of writing is to use table linking. If table A and table B have a one-to-many relationship, scanning the records of B will exceed the need (the requirement is to find only one, and the JOIN method is to find all ), in addition, due to the use of distinct, sorting operations may be required inside the database, and the efficiency will be affected to a certain extent.
The second way of writing is not standard SQL. Group by has only one field. Theoretically, the way of writing A.* in SELECT is not supported, and the efficiency should be similar to the first way.
In the third way of writing, the exists statement will be optimized into a semi-join by the database (this is the case in Oracle, MySQL needs to check the appropriate execution plan by itself), so it is the most efficient.