All geek questions in one place

Improve your choice not in

I hope this question is not asked, I tried to find similar questions, but I probably used the wrong keywords. In any case, I am faced with a situation where I have to look for customers who have never placed an order before, so the request is performed as follows:

SELECT * FROM customers where customers_id NOT IN (SELECT customers_id FROM orders)

I read that it's nice to have nested SELECT s, so what's the best option?

Also, should I do select * or should indicate fields, I also read somewhere that select * best?

Thanks to everyone in advance.

+4
source share
2 answers
 SELECT * FROM customers LEFT JOIN orders ON customers.customers_id = orders.customers_id WHERE orders.customers_id IS NULL
+5
source

Easy question: SELECT * more expensive than selecting some fields, it can even be dramatic. Less data, no access to the table if all fields are in the index. Lack of marshalling from the value of the database field for a programming language variable.

Nested subqueries and joints are eligible.

 SELECT * FROM customers where customers_id NOT IN (SELECT customers_id FROM orders)

can be rewritten as:

 SELECT * FROM customers c WHERE NOT EXISTS(SELECT * FROM orders o WHERE o.customers_id = c.customers_id)

MySQL says that it can better optimize EXISTS, since recursively optimized choices now have information about c.customers_id.

Although it will be best to JOIN @StilesCrisis, although I doubt it is correct. (I didn't drink my first cup of coffee during the day, though.)

BTW. EXISTS(SELECT * does not actually reserve space for all fields, and everything is in order.

+2
source

Source: https://habr.com/ru/post/1394739/

More articles:


All Articles