Shulou(Shulou.com)06/01 Report--

This article mainly introduces how to use SQL sub-query in SQL, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

What is a subquery?

A subquery is an SQL query nested within another SQL query. They help queries create conditions for WHERE clauses to filter rows and perform actions on them. Subqueries can use SELECT,INSERT,UPDATE, and DELETE statements.

Example of a subquery in the WHERE clause

We want to get users in the database where the transaction amount is greater than 1000 yuan. For our example, we will have two tables, users and transactions, which store user and transaction information.

We can write a query that extracts all rows of more than $1000 from the transactions table and then uses them as a condition for another query, which extracts rows from the table based on the results of the first query by users.

The query will look like this:

SELECT * FROM usersWHERE id IN (SELECT user_id FROM transactions WHERE amount > 1000); advantages of subquery

Compared with joins, subqueries improve query readability by building them into separate parts.

Easy to understand and maintain subqueries.

Subqueries can replace complex joins and joins.

Disadvantages of subquery

A subquery cannot modify a table and select from the same table in the same SQL statement.

Subqueries are an expensive task, so it is faster to use join operations.

Run a subquery activity

We will explore some examples of using Arctype, where we will show subqueries and see how they are built and used in an application.

We need to create a database that we will use in this tutorial. For convenience, I created a GitHub Gist containing the SQL file to build our database schema and insert virtual data into it. To use it, we need to do the following:

Use Arctype to connect to our database.

Navigate to the query tab in Arctype and create a new query.

Paste and run the create.sql file in Gist to create the schema.

Navigate to the query tab again to create a new query.

Paste and run the insert.sql file in Gist to populate the database with virtual data.

Use subqueries to select data

Let's write a query to select all rows from the SKU_ data table referenced in the BUYER table. Create a new query in Arctype and execute the following code:

SELECT * FROM BUYERWHERE BuyerName IN (SELECT BUYER FROM SKU_DATA)

In the above code, we created an internal query that BUYER selects columns from the SKU_ data table and then uses it as a condition to select rows with the same BuyerName column values from the BUYER table.

Update data using subqueries

Let's write a query that increases the values listed in the ORDER_ item table of Price for all goods sold in 2016 by 10%. Create a new query in Arctype and execute the following code:

UPDATE ORDER_ITEMSET Price=Price*1.1WHERE SKU IN (SELECT SKU FROM CATALOG_SKU_2016)

In the above code, we created an internal query that SKU selects columns from the CATALOG_SKU_ 2016 table to filter the rows that we should update in the ORDER_ITEM table.

Delete data using subqueries

We will write a subquery that removes all records from the table that INVENTORY stores in a warehouse of less than 130000 square feet. The query will look like this:

DELETEFROM INVENTORYWHERE WarehouseID IN (SELECT WarehouseID FROM WAREHOUSE WHERE SquareFeet

< 130000);使用嵌套子查询 也可以在另一个子查询中包含子查询。下面是一个例子： SELECT *FROM CATALOG_SKU_2017WHERE SKU IN ( SELECT SKU FROM INVENTORY WHERE WarehouseID IN ( SELECT WarehouseID FROM WAREHOUSE WHERE SquareFeet >

130000); Thank you for reading this article carefully. I hope the article "how to use SQL Sub-query in SQL" shared by the editor will be helpful to everyone. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.