Using the subquery, we can also find the Minimum, Maximum, and Average number of items from the Budget table. We also refer to this temporary table as a derived or materialized subquery. When using a subquery with the FROM clause, the database engine treats the resulting set as a temporary table where each row represents a record. SELECT Name, CustomerNum FROM Customers WHERE CustomerNum NOT IN (SELECT DISTINCT CustomerNum FROM Orders) Now let us find the customers who have not placed any orders using the subquery with the NOT IN operator. We will first create the table, then insert some demo data. CREATE TABLE Orders (OrderID int NOT NULL PRIMARY KEY, CustomerNum int, status varchar (255), OrderNum int, Budget int ) We have two tables suppose ‘Customers’ (CustomerID, Name, Payment_purpose, CustomerNum, Amount) and ‘Orders’ (OrderID, OrderNum, Status, CustomerNum). When a subquery provides more than a single value, you can add other operators with WHERE clauses, such as IN and NOT IN. MySQL Subquery with operators IN and NOT IN Here, we have used the AVG aggregate function in SQL inner query statement, which produces the following results through an outer query.Ģ. In addition to this, you can also execute a subquery using the equality operator and another comparison like greater than (>) or smaller than ( (SELECT AVG(amount) FROM Customers) This subquery with the WHERE clause returns the customer details whose payment amount is maximum in the table Customer. We have used the following query to fetch the details required using a Subquery with comparison operators to return a value and with the expression in the WHERE clause: SELECT Name, CustomerNum, Payment_purpose FROM Customers WHERE amount = (SELECT MAX(amount) FROM Customers) CREATE TABLE Customers ( CustomerID int NOT NULL PRIMARY KEY, Name varchar(255), CustomerNum int, Payment_purpose varchar(255), Amount int ) Īnd also inserted some data samples as follows: INSERT INTO Customers (CustomerID, Name, CustomerNum, Payment_purpose, Amount) VALUES ('1', 'Nikhil', '21', 'Rent', '4000') The table has CustomerID, Name, Payment_purpose, CustomerNum, and Amount fields. Let us prepare a demo data database table named Customer, having information about customers and payment amounts. Types of MySQL Subqueryįollowing are the types of subquery with syntaxes and examples: 1. When we run this query, we can say that the query inside the outer executes first, and the result it returns is used as input for the outer query. Then the outer query selects all the names from the PersonDb table that work in PersonIT whose PersonID is matched from the result set of the inner query. In the above SQL Statement, the subquery first returns all the PersonIDs of PersonIT located in the city of Delhi. SELECT PersonName FROM PersonDb WHERE PersonID IN (SELECT PersonID FROM PersonIT WHERE City='Delhi') Suppose we have the following SQL query, which provides us with the persons who work in the IT sector located in Delhi. In MySQL, we can use subqueries in any SQL query expression, but we need to enclose them within parentheses. Let us explain how to use the MySQL Subquery to write any compound queries and learn the correlated Subquery concept. Hence, the primary use of subquery can be to compute an instant value for another query to be executed. But JOINS returns rows, and the subquery returns either a single value as a result of a row set. Thus, like JOINS, a Subquery combines table data from different tables into a single result set.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |