In VS or in the SQL Where Clause

IN vs OR in the SQL WHERE clause

I assume you want to know the performance difference between the following:

WHERE foo IN ('a', 'b', 'c')
WHERE foo = 'a' OR foo = 'b' OR foo = 'c'

According to the manual for MySQL if the values are constant IN sorts the list and then uses a binary search. I would imagine that OR evaluates them one by one in no particular order. So IN is faster in some circumstances.

The best way to know is to profile both on your database with your specific data to see which is faster.

I tried both on a MySQL with 1000000 rows. When the column is indexed there is no discernable difference in performance - both are nearly instant. When the column is not indexed I got these results:

SELECT COUNT(*) FROM t_inner WHERE val IN (1000, 2000, 3000, 4000, 5000, 6000, 7000, 8000, 9000);
1 row fetched in 0.0032 (1.2679 seconds)

SELECT COUNT(*) FROM t_inner WHERE val = 1000 OR val = 2000 OR val = 3000 OR val = 4000 OR val = 5000 OR val = 6000 OR val = 7000 OR val = 8000 OR val = 9000;
1 row fetched in 0.0026 (1.7385 seconds)

So in this case the method using OR is about 30% slower. Adding more terms makes the difference larger. Results may vary on other databases and on other data.

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

Using sql with in where clause

You need to define your WITH CTE before calling the SQL:

WITH TableList (tableId)
AS
(
    SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
    UNION ALL
    SELECT t.tableId From OldTable as t
        INNER JOIN TableList as p
            ON t.ParenTableId = p.TableId
    WHERE t.IsDeleted = 0
)
SELECT * 
FROM TestTable 
WHERE tableId in 
(
    SELECT tableId FROM TableList
)

EDIT -- Based on your comments, you could define your Recursive CTE in a VIEW and then use as needed:

CREATE VIEW YourView AS 
WITH TableList (tableId)
AS
(
    SELECT tableId FROM OldTable WHERE tableId=5 and IsDeleted = 0
    UNION ALL
    SELECT t.tableId From OldTable as t
        INNER JOIN TableList as p
            ON t.ParenTableId = p.TableId
    WHERE t.IsDeleted = 0
)
SELECT * 
FROM TableList;

SQL Server: Select in vs or?

Due to Sql Server's optimization of queries these will run at the same speed since they are logically equivalent.

i favor the IN syntax for brevity and readability though.

MySQL OR vs IN performance

The accepted answer doesn't explain the reason.

Below are quoted from High Performance MySQL, 3rd Edition.

In many database servers, IN() is just a synonym for multiple OR clauses, because the two are logically equivalent. Not so in MySQL, which sorts the values in the IN() list and uses a fast binary search to see whether a value is in the list. This is O(Log n) in the size of the list, whereas an equivalent series of OR clauses is O(n) in the size of the list (i.e., much slower for large lists)

---

Related Topics