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

This article mainly introduces the "Mariadb select sub-query and how to use UNION" related knowledge, editor through the actual case to show you the operation process, the method of operation is simple and fast, practical, I hope that this "Mariadb select sub-query and how to use UNION" article can help you solve the problem.

Select subquery

SELECT queries nested in other SELECT statements are called subqueries. Why do you do this? In fact, we have learned multi-table query, many times multi-table query is enough? But the subquery has its own status and value, and take the example of multi-table query.

If we want to know the language used in a city, it can be divided into two steps:

1. Query the city's CountryCode in the City table.

two。 Use the CountryCode you query to query the language used in the country in the CountryLanguage table.

Although it can be done in two steps, it requires two queries and two transmissions, and in terms of bandwidth and performance, we prefer Mysql (MariaDB) to help us do this, don't we?

Let's see what it's like to use a subquery.

MariaDB [world] > SELECT Language FROM countrylanguage WHERE CountryCode = (SELECT CountryCode FROM city WHERE Name = 'Peking'); +-+ | Language | +-+ | Chinese | | Dong | | Hui | | Mant roomu | | Miao | | Mongolian | | Puyi | | Tibetan | | Tujia | | Uighur | | Yi | Zhuang | +-+ 12 rows in set (0.07sec)

Do you see this piece (SELECT CountryCode FROM city WHERE Name = 'Peking')? If the query is enclosed in parentheses, he will get the CountryCode of Beijing, and this CountryCode will be used as the WHERE search condition of the outside SELECT.

Therefore, a subquery is a query enclosed in parentheses, and MariaDB will first perform a query in parentheses to get a value or a set of values to replace it with the appropriate location.

ANY or SOME subquery

In fact, ANY and SOME have the same meaning as IN, as long as the operation that satisfies any value in the operator pair () is TRUE, as shown below.

Check with all employees of the Technical Department (Tech) and the sales Department (Sales):

MariaDB [world] > SELECT * FROM user-> WHERE deptid IN-> (SELECT id FROM department WHERE name IN ('Sales','Tech')); MariaDB [world] > SELECT * FROM user-> WHERE deptid = ANY-> (SELECT id FROM department WHERE name IN (' Sales','Tech')) MariaDB [world] > SELECT * FROM user-> WHERE deptid = SOME-> (SELECT id FROM department WHERE name IN ('Sales','Tech')) +-+ | id | name | password | regtime | deptid | + -+ | 1 | test | test | 2018-03-05 17:25:26 | 1 | | 2 | test1 | test1 | 2018-03-05 17:25:26 | 1 | | 3 | lucy | lucy | 2018-03-05 17:25:26 | 2 | +-+- +-+ 3 rows in set (0.00 sec)

EXISTS existence judgment

As long as the value returned by the subquery is TRUE, otherwise it is FALSE, as shown in the following example:

MariaDB [world] > SELECT EXISTS (SELECT * FROM user WHERE deptid = (SELECT id FROM department WHERE name='Tech')) AS dep_is_Exist;+-+ | dep_is_Exist | +-+ | 1 | +-+ 1 row in set (0.01sec)

Of course, you can add a NOT before EXISTS, so it becomes true when the subquery has no result.

Matters needing attention

The SELECT statement as a subquery can only query a single column, and an error will be returned if you attempt to retrieve multiple columns. General subqueries and table joins can be transformed into each other, and table joins are relatively more efficient than subqueries. UNION combinatorial table

SQL allows you to execute multiple queries (multiple SELECT statements) and return the results as a query result set, often referred to as union or compound query.

You should pay attention to the following points:

The UNION must consist of two or more SELECT statements separated by the keyword UNION (therefore, if you combine four SELECT statements, you will use three UNION keywords). Each query in UNION must contain the same column, expression, or aggregate function (however, each column does not need to be listed in the same order). Column data types must be compatible: the type does not have to be identical, but it must be a type that DBMS can implicitly convert (for example, different numeric types or different date types).

The syntax is as follows:

SELECT column_name FROM table1UNIONSELECT column_name FROM table2

Look at the following example to retrieve all countries in the United States and China:

MariaDB [world] > SELECT * FROM city WHERE CountryCode = 'CHN'-> UNION-> SELECT * FROM city WHERE CountryCode =' USA' +-+-+ | ID | Name | CountryCode | District | Population | +-+-- -- +-+-- +-+ | 1890 | Shanghai | CHN | Shanghai | 9696300 | | 1891 | Peking | CHN | Peking | | 7472000 |.... | 4064 | Odessa | USA | Texas | | | 89293 | | 4065 | Carson | USA | California | 89089 | | 4066 | Charleston | USA | South Carolina | 89063 | +-| -+-+ 637 rows in set / / the following statement is equivalent to the above SELECT query SELECT * FROM city WHERE CountryCode = 'USA' OR CountryCode =' CHN'

Count the number of cities in the United States and China:

MariaDB [world] > SELECT CountryCode,COUNT (id) AS city_nums FROM city WHERE CountryCode = 'CHN'-> UNION-> SELECT COUNT (id), CountryCode FROM city WHERE CountryCode =' USA' +-+-+ | CountryCode | city_nums | +-+-+ | CHN | 363,274 | USA | +-+-+ 2 rows in set (0.00 sec)

So as long as the number of columns is the same, they can all be combined into the same result set. The first row of the following example shows the number of cities in the United States, and the second line shows the number of languages in the United States:

MariaDB [world] > SELECT COUNT (id), CountryCode FROM city WHERE CountryCode = 'USA' UNION SELECT COUNT (Language), CountryCode FROM countrylanguage WHERE CountryCode =' USA' +-+-+ | COUNT (id) | CountryCode | +-+-+ | 274 | USA | | 12 | USA | +-+-+ 2 rows in set (0.00 sec) This is the end of the Mariadb select subquery and how to use UNION. Thank you for your reading. If you want to know more about the industry, you can follow the industry information channel. The editor will update different knowledge points for you every day.

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.