A 38-Year-Old's MYSQL Primer 05 — Correlated Subqueries

A 38-year-old's MYSQL primer 05 — correlated subqueries 

Alright, step five. Picking a radish isn't easy. Once you've pulled it out, saying 'nope' doesn't help. Don't worry about dirty pants or sleeves — just crouch down and inspect every corner. 

(Heads-up: I use the same practice tables, but my examples won't match the book's one-to-one. ><) 

0. These little slip-ups can happen. 

If there's one thing that frustrates me as a non-major learning dev as a latecomer, it's that no one ever tells you the supposedly 'obvious' stuff. Before each post I'll jot down whatever I remember. 

1) Nothing comes to mind yet — leaving it blank for now~ ;ㅇ

1. EXISTS - with the EXISTS predicate you can judge the situation of another table (judge whether the subquery result exists or not) and use the subquery inside UPDATE, SELECT, or DELETE.

1) First, let's look at the initial state of the test tables. We'll use these two tables.

 select * from sample551; 

 select * from sample552; 

2) A practice for checking whether data exists using the EXISTS predicate. (Use when rows matching the subquery's condition exist)

Unlike the book, I set aliases on each table (s551, s552) and compared conditions in the EXISTS subquery via those aliases. You can also skip aliases and write 'table.column' = 'sample552.no2', or skip declarations entirely as in the book. Personally I want to build the habit so I use aliases — once a database grows, column names can collide ;D But during practice, go whatever way feels comfy~

A rough read of the code:

We will update    

s551    

setting    

column 'a' to    'yes'

when 

we check (there exists a row matching the subquery)

(select     everything     from 

s552

when

s552's no2 equals         s551's no)

;

update 
  sample551 s551
set 
  a='있음' 
where
  exists
     (select * from 
        sample552 s552
      where 
        s552.no2 = s551.no
     );

 select * from sample551; 

2. NOT EXISTS - NOT lets you negate the value. (Use when no row matching the subquery's condition exists)

update 
  sample551 s551
set 
  a='없음' 
where
  not exists
     (select * from 
        sample552 s552
      where 
        s552.no2 = s551.no
     ); 

 select * from sample551; 

3. Correlated subquery - a kind of subquery that's combined with an EXISTS predicate.

Today's first example is also a correlated subquery — because the parent command (UPDATE SET) forms a specific relationship with the child subquery (SELECT FROM WHERE). 

Unlike the queries in the previous post, a correlated subquery can't be run in isolation.

For example, in update sample551 s551 set a='yes' where exists (select * from sample552 s552 where s552.no2 = s551.no);, the trailing subquery 

select * from sample552 where sample552.no2 = sample551.no;

can't stand alone. You get an error like below. In a correlated subquery the parent command is required for processing. Makes sense — if the SELECT is on sample552 but the WHERE refers to sample551.no, the subquery alone has no way to know. 

4. IN - when comparing scalar (single-result) values, you use the = operator. But you can't compare sets that way. That's when you use IN, which lets you compare sets.

IN is true when a value is included in the set. NOT IN is true when it's not. Unlike aggregate functions, IN does not ignore NULLs. But to compare, use IS NULL, not = NULL. Also, with NOT IN, if either side contains NULL, the result is N.

1) That means the earlier correlated-subquery example could also be written as a plain query using IN. Let's try.

update 
  sample551 s551
set 
  a='있음요' 
where
  s551.no in(3,5);

 select * from sample551; 

+ α )   Or, just using 'or' would also work.

update 
  sample551 s551
set 
  a='있죠' 
where
  s551.no = 3 or s551.no = 5;

 select * from sample551; 

+ α )   Or, since we practiced subqueries, we can rewrite it with a subquery as below. This way what we mentioned in section 3 about correlated subqueries — 'a correlated subquery can't be run in isolation' — no longer applies.

update 
  sample551 s551
set 
  a='있쑴다' 
where
  s551.no in (select no2 from sample552); 

 select * from sample551; 

Oho~ correlated subqueries — done~ ;D Let's wrap up MySQL and move on to MariaDB~