Querying correlated data

You can query one table relative to the data in another table.

A correlated query contains a query predicate with the equals (=) operator. One side of the operator must reference at least one column from the parent query and the other side must reference at least one column from the subquery. An uncorrelated query does not reference any columns in the parent query.

Select all state and net_payments values from the transfer_payments table for years during which the value of the state column in the transfer_payments table matches the value of the state column in the us_census table.

SELECT state, net_payments
FROM transfer_payments
WHERE EXISTS 
   (SELECT year 
   FROM us_census 
   WHERE transfer_payments.state = us_census.state);

This query is correlated because one side of the equals predicate operator in the subquery references the state column in the transfer_payments table in the parent query and the other side of the operator references the state column in the us_census table.

This statement includes a conjunct in the WHERE clause.

A conjunct is equivalent to the AND condition, while a disjunct is the equivalent of the OR condition The following subquery contains a conjunct:

... WHERE transfer_payments.year = "2018" AND us_census.state =
                  "california"

The following subquery contains a disjunct:

... WHERE transfer_payments.year = "2018" OR us_census.state =
                  "california"