MySQL Subqueries PDF

Chapter 7 How to code subqueries Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 1  Objectives Applied  Code SELECT statements that require subqueries. Knowledge  Describe the way subqueries can be used in the WHERE, HAVING,...

Chapter 7 How to code subqueries Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 1  Objectives Applied  Code SELECT statements that require subqueries. Knowledge  Describe the way subqueries can be used in the WHERE, HAVING, FROM and SELECT clauses of a SELECT statement.  Describe the difference between a correlated subquery and a noncorrelated subquery. Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 2  Four ways to introduce a subquery in a SELECT statement 1. In a WHERE clause as a search condition 2. In a HAVING clause as a search condition 3. In the FROM clause as a table specification 4. In the SELECT clause as a column specification Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 3  A subquery in a WHERE clause SELECT invoice_number, invoice_date, invoice_total FROM invoices WHERE invoice_total > (SELECT AVG(invoice_total) FROM invoices) ORDER BY invoice_total The value returned by the subquery 1879.741316 The result set (21 rows) Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 4  A query that uses an inner join SELECT invoice_number, invoice_date, invoice_total FROM invoices JOIN vendors ON invoices.vendor_id = vendors.vendor_id WHERE vendor_state = 'CA' ORDER BY invoice_date The result set (40 rows) Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 5  The same query restated with a subquery SELECT invoice_number, invoice_date, invoice_total FROM invoices WHERE vendor_id IN (SELECT vendor_id FROM vendors WHERE vendor_state = 'CA') ORDER BY invoice_date The same result set (40 rows) Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 6  Advantages of joins  A join can include columns from both tables.  A join is more intuitive when it uses an existing relationship. Advantages of subqueries  A subquery can pass an aggregate value to the main query.  A subquery is more intuitive when it uses an ad hoc relationship.  Long, complex queries can be easier to code using subqueries. Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 7  The syntax of a WHERE clause that uses an IN phrase WHERE test_expression [NOT] IN (subquery) A query that gets vendors without invoices SELECT vendor_id, vendor_name, vendor_state FROM vendors WHERE vendor_id NOT IN (SELECT DISTINCT vendor_id FROM invoices) ORDER BY vendor_id Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 8  The result of the subquery (34 rows) The result set (88 rows) Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 9  The query restated without a subquery SELECT v.vendor_id, vendor_name, vendor_state FROM vendors v LEFT JOIN invoices i ON v.vendor_id = i.vendor_id WHERE i.vendor_id IS NULL ORDER BY v.vendor_id Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 10  The syntax of a WHERE clause that uses a comparison operator WHERE expression comparison_operator [SOME|ANY|ALL] (subquery) A query with a subquery in a WHERE condition SELECT invoice_number, invoice_date, invoice_total - payment_total - credit_total AS balance_due FROM invoices WHERE invoice_total - payment_total - credit_total > 0 AND invoice_total - payment_total - credit_total < ( SELECT AVG(invoice_total - payment_total – credit_total) FROM invoices WHERE invoice_total - payment_total - credit_total > 0 ) ORDER BY invoice_total DESC Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 11  The value returned by the subquery 2910.947273 The result set (9 rows) Murach's MySQL, C7 © 2015, Mike Murach & Associates, Inc. Slide 12  How the ALL keyword works Condition Equivalent expression x > ALL (1, 2) x>2 x < ALL (1, 2) x ANY (1, 2) x>1 x < ANY (1, 2) x