A 38-year-old's MYSQL primer 04
Alright, this is step two. We've reached the radish field — now let's pick a radish worth pulling.
Oh, 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 — on standby.
1. Subquery (1)
Having done a lap around Oracle, I can say subqueries are important. In the book, they sit just past the halfway mark. Let's go with the feel of 'now we're actually starting.'
A subquery is:
- a data query expressed via a SELECT. <-- I don't mean 'used in the SELECT clause' ;D I mean the subquery itself is a SELECT statement~
- A supporting query nested under another, not the top-level one.
- It's used a lot in the WHERE clause.
- Remember, WHERE can be used in SELECT, DELETE, and UPDATE.
Before moving on — a quick Google shows another blog summarizes it like this:
A subquery is another query contained inside a query.
It's a dependent relationship in which the main query contains the subquery.
Put simply, you're writing a query inside a query.
Note: a Sub Query is sometimes also called an Inner Query.
I left the link — check it out if you're interested; there are examples too.
1) Before the exercise, let's check the example table.
select * from sample54; 
2) Using a subquery in a DELETE statement.
(1) This one's a bit tricky. If you shape the query by the definition above, you get the statement below. But when you run it, you get the following error.
delete
from
sample54
where
a=(select min(a) from sample54); 
(2) The book suggests using a variable or swapping DELETE for SELECT... the former only works in MySQL, and the latter is a non-starter because it changes what you're doing. So I searched. The cause is explained below. The workaround is behind the link.
Following the guide above, here's the fixed query. It works ^^ thanks! I hope my blog also helps someone like that someday hahaha — keep it up!
delete
from
sample54
where
a=(select * from
(select min(a) from sample54) tmp
); To explain the fix:
Because MySQL can't use the same table's data directly,
instead of comparing 'a' to the subquery directly as before,
we wrap the subquery inside (select * from tmp); and then compare.
select * from sample54; 
3) Scalar — fancy name for something that sounds hard. It refers to a SELECT that returns a single value — said to 'return a scalar value.' In short, a single value. Why the name? Apparently because it gets heavy use in subqueries. A subquery produces conditions for the main query, and if it returns multiple rows, the main query has a hard time comparing. Makes sense... I think? Roughly that's the feel...
A bit unsatisfied, I Googled more — not much material. I did find a YouTube video worth linking.
4) Using a subquery in the SELECT clause
Syntactically a subquery is treated as 'a single item.' Correct syntax doesn't mean no errors — being able to say something doesn't mean what you said is right ha. That's why you need a scalar subquery. Of course, a scalar subquery is also needed when used inside the SELECT clause. I.e., a subquery that returns a single value.
The example below is a SELECT made of two subqueries that each return a count of a table. Because it's a count, the value is 1 — so you can say it returns a scalar value.
select
(select count(*) from sample51) as sq1,
(select count(*) from sample54) as sq2
from
dual; 
+ α ) 'dual' is a system-created default table in the database. In MySQL it can even be omitted... but let's just write it ;D
5) Using a subquery in the UPDATE SET clause — let's blanket the example table with its maximum value via UPDATE SET.
update
sample54
set
a=(select * from
(select max(a) from sample54) tmp
); select * from sample54; 
6) Using a subquery in the FROM clause — good news. Subqueries in FROM don't have to be scalar. Why? Feel it? A subquery in FROM isn't a filter for pruning; it's a basket for collecting — that's how I understood it.
select * from
(select * from sample54) sq; select * from sample54; 
+ α ) Bonus — though not often used, subqueries in FROM can be nested up to three levels deep in the same clause. The query looks like below. The result is the same.
select * from
(select * from
(select * from sample54) sq1)
sq2; + α ) Also — in real-world work, people often reorder the list produced by a subquery and pick just the top few. Like the example below. The sample table's values don't give meaningful ordering, but get the vibe of it.
select * from
(select * from sample54 order by a desc) sq
limit 2; 
+ α ) Also, in ORACLE, instead of limit you use where rownum<=number_of_rows, right?
select * from
(select * from sample54 order by a desc) sq
where rownum <= 2; 7) Using a subquery in an INSERT statement
Before we start, a note: I checked and sample541 is an empty table.
(1) The subquery has to be a scalar subquery, and the VALUES in INSERT have to match the data type of each column.
insert into
sample541
values (
(select count(*) from sample51),
(select count(*) from sample54)
); select * from sample541; 
(2) Another way to use a subquery is INSERT SELECT.
a) Equivalent to insert into sample541 values(1,2)
insert into sample541 select 1,2; b) Appends the full result of the SELECT into the table specified by INSERT INTO. Instead of returning rows to the client, they're added to the target table. Commonly used for copying or moving data. If the copy target has the same column layout and types, you can even copy rows as-is.
insert into sample542 select * from sample543; 
But the book's example didn't run for me. I checked for typos, even changed numbers — still no luck. Looks like the table doesn't exist ;>
So I searched online: W3schools has examples~ I left a link, check it out if you have time.
Hmm, what's next, hmm..
Boom-chick-
I'm hungry..
That's it for today~ ;D
