Click above SQL database development, pay attention to get SQL video tutorial \
SQL column
Summary of SQL basic knowledge
SQL advanced knowledge summary
IN the role of
IN can specify multiple values IN WHERE itself, and is a shorthand for multiple OR conditions.
IN grammar
SELECT column_name(s) FROM table_name
WHERE column_name [NOT] IN (value1, value2, …) ;
or
SELECT column_name(s) FROM table_name
WHERE column_name [NOT] IN (SELECT STATEMENT);
The first syntax is to manually enter multiple values that you need to filter directly IN parentheses.
The second syntax takes the result of the subquery in parentheses as the value to which you filter the data.
The sample data
We take customers as the demonstration data, which is as follows:
Customers table
IN the instance of the
We want to query “Beijing”, “Shanghai”, “Guangzhou” from customers:
SELECT * FROM customers
WHERE ` city ` IN ('Beijing'.'Shanghai'.'guangzhou')
Copy the codeThe result is: \
We can also use OR to do this. The following statement does the same thing:
SELECT * FROM customers
WHERE ` city ` ='Beijing'
OR ` city `='Shanghai'
OR ` city `='guangzhou'
Copy the codeHowever, using the OR code can seem tedious, so if you encounter multiple values for the same field, it is recommended to use IN to write. \
Instance of NOT IN
If there is equality, there will be inequality, and so will IN, which can be combined with NOT to indicate functions that do NOT exist.
We want to query customers’ city not “Beijing”, “Shanghai” or “Guangzhou” from the table customers:
SELECT * FROM customers
WHERE ` city ` NOT IN ('Beijing'.'Shanghai'.'guangzhou')
Copy the codeThe result is: \
Notice that the city column does NOT contain “Beijing”, “Shanghai” or “Guangzhou” IN our query results, which is exactly the data we need to find, and this is the function of NOT IN.
IN sub-query instance
As in the next supplier Table, an example is as follows:
To find customers in the same city as the supplier, we could write:
SELECT * FROM Customers WHERE city IN (SELECT city FROM Suppliers);Copy the codeThe result is: \
The IN is followed by a subquery, which we’ll talk about later. The function of the sub-query here is to query the city information in the list of suppliers. We can see the information of “Shanghai”, “Nanjing” and “Xi ‘an”.
At this time, IN filters the customer information IN the customers table through the information of the three cities. As long as the cities in the Customers table are among the three cities, this is the data we need.
Here you can think of IN as taking the data IN both tables and getting it out.
-- End -- background reply keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Just ran a "Hello world" with Huawei Hongmeng! SQL learning roadmap, the first open cisco former staff delete database run, loss up to1600More than ten thousand Sun Wukong does not have surname nameless time, how is yan King's life and death book written? Tongji edition of "Linear Algebra" caused a fierce controversy this is a can learn technology public account, welcome to pay attention toCopy the code