subqueries in mysql

subqueries in mysql

joins perform better than subqueries, so it’s often more efficient to rewrite a subquery as a join when possible.


SELECT CONCAT_WS(‘ ‘, AuthFN, AuthMN, AuthLN) AS Author
FROM Authors
WHERE AuthID=
( SELECT ab.AuthID
FROM AuthorBook AS ab, Books AS b
WHERE ab.BookID=b.BookID AND BookTitle=’Noncomformity’
);

---------------------------------------------------------------------------------------------------------------
SELECT DISTINCT CONCAT_WS(‘ ‘, AuthFN, AuthMN, AuthLN) AS Author
FROM Authors AS a JOIN AuthorBook AS ab ON a.AuthID=ab.AuthID
JOIN Books AS b ON ab.BookID=b.BookID
WHERE BookTitle=’Noncomformity’;

In this case, rather than using a subquery to retrieve the data, you use a join

SELECT DISTINCT CONCAT_WS(‘ ‘, AuthFN, AuthMN, AuthLN) AS Author
FROM Authors
WHERE AuthID <>
(
SELECT ab.AuthID
FROM AuthorBook AS ab, Books AS b
WHERE ab.BookID=b.BookID AND BookTitle=’Noncomformity’
)
ORDER BY AuthLN;

The ANY and SOME Operators

The ANY and SOME operators, which are synonymous, allow you to create an expression that compares a column to any of the values returned by a subquery

SELECT BookTitle, Copyright
FROM Books
WHERE Copyright > ANY
(
SELECT b.copyright
FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
JOIN Authors AS a USING (AuthID)
WHERE AuthLN=’Proulx’
)
ORDER BY BookTitle;

In this case, the subquery returns a list of Copyright values (1992 and 1993) for the books written by
the author Proulx. Those values are used in the WHERE clause expression, which can be interpreted as Copyright > ANY (1992, 1993). Because the statement uses the ANY operator, a value in the
Copyright column must be greater than either 1992 or 1993, so the expression can now be interpreted
as Copyright>1992 OR Copyright>1993.

The ALL Operator

The ALL operator is different from the ANY and SOME operators because it requires that all values returned by the subquery must cause the expression to evaluate to true before the outer statement can return a row.

SELECT BookTitle, Copyright
FROM Books
WHERE Copyright > ALL
(
SELECT b.copyright
FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
JOIN Authors AS a USING (AuthID)
WHERE AuthLN=’Proulx’
)
ORDER BY BookTitle;

The IN and NOT IN Operators

SELECT BookTitle, Copyright
FROM Books
WHERE Copyright IN
(
SELECT b.copyright
FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
JOIN Authors AS a USING (AuthID)
WHERE AuthLN=’Proulx’
)
ORDER BY BookTitle;

----------------------------------------------------------------------------------------

SELECT BookTitle, Copyright
FROM Books
WHERE Copyright NOT IN
(
SELECT b.copyright
FROM Books AS b JOIN AuthorBook AS ab USING (BookID)
JOIN Authors AS a USING (AuthID)
WHERE AuthLN=’Proulx’
)
ORDER BY BookTitle;

The EXISTS and NOT EXISTS Operators

SELECT BookID, BookTitle
FROM Books AS b
WHERE EXISTS
(
SELECT BookID
FROM AuthorBook AS ab
WHERE b.BookID=ab.BookID
)
ORDER BY BookTitle;

----------------------------------------------------------------------------------

SELECT BookID, BookTitle
FROM Books AS b
WHERE NOT EXISTS
(
SELECT BookID
FROM AuthorBook AS ab
WHERE b.BookID=ab.BookID
)
ORDER BY BookTitle;

-------------------------------------------------------------------

SELECT OrderID, SUM(Quantity) AS Total
FROM BookOrders
GROUP BY OrderID
HAVING Total>(SELECT AVG(Quantity) FROM BookOrders);

----------------------------------------------------------------------------------------

SELECT DVDName
FROM DVDs
WHERE StatID<>(SELECT StatID FROM Status WHERE StatDescrip=’Available’)
ORDER BY DVDName;

------------------------------------------------------------------------------------------------

UPDATE Books
SET BookTitle=’The Way of Zen’, Copyright=1957
WHERE BookID=
( SELECT ab.BookID FROM Authors AS a, AuthorBook AS ab
WHERE a.AuthID=ab.AuthID AND a.AuthLN=’Watts’
);

DELETE ab, b
FROM AuthorBook AS ab, Books AS b
WHERE ab.BookID=b.BookID
AND ab.AuthID=(SELECT AuthID FROM Authors WHERE AuthLN=’Watts’);
UPDATE Orders
SET EmpID=(SELECT EmpID FROM Employees WHERE EmpLN=’Reynolds’)
WHERE OrderID=14;

SELECT AuthFN, AuthMN, AuthLN FROM Authors
UNION
SELECT AuthFN, AuthMN, AuthLN FROM Authors2;

(SELECT AuthFN, AuthMN, AuthLN FROM Authors)
UNION
(SELECT AuthFN, AuthMN, AuthLN FROM Authors2)
ORDER BY AuthLN;



SELECT (
SELECT COUNT(*)
FROM user_table
) AS tot_user,
(
SELECT COUNT(*)
FROM cat_table
) AS tot_cat,
(
SELECT COUNT(*)
FROM course_table
) AS tot_course



SELECT name, headofstate, population
FROM Country
WHERE population=(SELECT MAX(population) FROM Country);