How Does the in Predicate Work in SQL

How does the IN predicate work in SQL?

The warning you got about subqueries executing for each row is true -- for correlated subqueries.

SELECT COUNT(*) FROM Table1 a 
WHERE a.Table1id NOT IN (
  SELECT b.Table1Id FROM Table2 b WHERE b.id_user = a.id_user
);

Note that the subquery references the id_user column of the outer query. The value of id_user on each row of Table1 may be different. So the subquery's result will likely be different, depending on the current row in the outer query. The RDBMS must execute the subquery many times, once for each row in the outer query.

The example you tested is a non-correlated subquery. Most modern RDBMS optimizers worth their salt should be able to tell when the subquery's result doesn't depend on the values in each row of the outer query. In that case, the RDBMS runs the subquery a single time, caches its result, and uses it repeatedly for the predicate in the outer query.

PS: In SQL, IN() is called a "predicate," not a statement. A predicate is a part of the language that evaluates to either true or false, but cannot necessarily be executed independently as a statement. That is, you can't just run this as an SQL query: "2 IN (1,2,3);" Although this is a valid predicate, it's not a valid statement.

How to build an IN clause in JPA using Predicates?

Please try predicates.add(root.get("id").in(ids)));.

SQL Predicates per aggregate function

You can do conditional aggregation.

Since is_dentist (presumably) contains 0/1 values, you can just sum() this column to count how many doctors belong to the group.

On the other hand, you can use another conditional sum() to count how many doctors have an income above the threshold.

select 
    h.name, 
    sum(d.is_dentist) no_dentists, 
    sum(d.monthly_income > 100000) no_doctors_above_100000_income
from Hospital h 
inner join Doctor d on h.id = d.hospital_id
group by h.name;

Selective Predicate Pushdown To View

you can add additional column src for determining the source table and wrap predicates in the CASE:

select * from
(
SELECT u.*, 'users' as src FROM users u
union all
SELECT uc.*, 'users_changes' as src FROM users_changes uc
) 
WHERE --applied only to users
      case when src  = 'users' 
                 then city = 'Chicago' --predicate wrapped in case
           else true
       end
  --applied to all
  AND account=12345 

---

Related Topics