MySQL: SUBQUERIES

SELECT first_name, last_name, hourly_pay,
(SELECT AVG(hourly_pay) FROM employees) AS avg_pay
FROM employees;

SELECT first_name, last_name
FROM employees
WHERE hourly_pay > (SELECT AVG(hourly_pay) FROM employees);

SELECT first_name, last_name
FROM customers
WHERE customer_id IN (SELECT DISTINCT customer_id
FROM transactions WHERE customer_id IS NOT NULL);

SELECT first_name, last_name
FROM customers
WHERE customer_id NOT IN (SELECT DISTINCT customer_id
FROM transactions WHERE customer_id IS NOT NULL);

🤩🤩🤩🤩🤩🤩🤩🤩🤩🤩🤩🤩🤩🤩

Thank you from Chile

godlike

I think you should touch on the performance too! These subqueries can be costly and suck up CPU in large tables. In the first example, the average was computed per row while $14.35 was needed only once. Other than that, great video. Thanks!

your videos are so concise!!!! Niceeeee

Bro code explains coding beautifully, but specifically for SQL I think "techtfq" channel is my personal favourite...

ok i was pretty frustrated until i started this video hearing Bro Code calling the manager as "Krabs" or craps? is this intentional? Bor Code is my type!

You are the bestttttttttttt❤❤❤

What about subqueries inside FROM?

love you bro

I finally got to understand this topic. This was amazing.
Kudos and Bravos😂

Or you can use a window function

After so many wrong and complicated examples on this topic finally a good and clear explanation, it helped me thanks.

I'm not sure if someone could answer this but I'll try:

SELECT first_name, last_name, hourly_pay
FROM employees
WHERE hourly_pay > (SELECT AVG(hourly_pay) FROM employees);

In this query, why can't we just, after the "greater than", write "AVG(hourly_pay)" ??

Excellent tutorial!

Great explanation
Thank you ☺️

Thanks, well explained

The customer and transactions example would be normally done with JOIN:
SELECT DISTINCT first_name, last_name FROM customers
JOIN transactions ON customers.customer_id = transactions.customer_id;
it's a bit shorter query, but it's cool to see that you can do this with other methods as well.

This looks like a nested "if" and "loop" statements in Java. I wouldn't be learning those without your tutorials in Java. And I know I will be learning those further when I start learning the other prog. languages you have on your playlist.

Thank you so much!