🎈 Write in front
🙋♂️ Hello everyone, I'm super dream. You can call me Xiaomeng~
We all know that we have to deal with the database almost every day, whether in study or daily work. In order to better operate the database, our SQL knowledge reserve is essential. If you want to master SQL well, you must practice and study every day. Next, Xiaomeng will lead the partners to brush a topic related to LeetCode database (SQL) every day, and then cite relevant knowledge points at the end of the article to help the partners learn and consolidate and better master SQL.
🙋♂️ If you don't understand anything in the learning process, you are welcome to leave a message and ask questions in the comment area. Xiaomeng will tell you everything.
catalogue
📒 The difference between union and union all
📕 SQL topic overview
World Table:
+-------------+---------+ | Column Name | Type | +-------------+---------+ | name | varchar | | continent | varchar | | area | int | | population | int | | gdp | int | +-------------+---------+
World Table: +-------------+-----------+---------+------------+--------------+ | name | continent | area | population | gdp | +-------------+-----------+---------+------------+--------------+ | Afghanistan | Asia | 652230 | 25500100 | 20343000000 | | Albania | Europe | 28748 | 2831741 | 12960000000 | | Algeria | Africa | 2381741 | 37100000 | 188681000000 | | Andorra | Europe | 468 | 78115 | 3712000000 | | Angola | Africa | 1246700 | 20609294 | 100990000000 | +-------------+-----------+---------+------------+--------------+
name is the primary key of this table.
Each row of this table provides: country name, continent, area, population and GDP value.
A country is considered to be a large country if it meets one of the following two conditions:
An area of at least 300 square kilometers (i.e. 3000000 km2), or
Population of at least 25 million (i.e. 25000000)
Write an SQL query to report the country name, population and area of a large country. Return the result table in any order.
The query result format is shown in the following example.
Output: +-------------+------------+---------+ | name | population | area | +-------------+------------+---------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +-------------+------------+---------+
📕 Problem solving ideas
This problem is a very simple one. Little friends can practice their hands
The topic should be from countries with an area of at least 300 square kilometers (i.e., 300000 km2) or a population of at least 25 million (i.e., 25000000), and output according to the format required by the topic. Given two conditions in the title, it is not difficult to know that we need to use the where clause to limit the conditions. Add the above conditions to the where clause from world where area >= 3000000 or population >= 25000000.
📕 Method implementation
📒 Method 1 - use or
We first use the where clause to filter records according to the two conditions required in the title.
Conditions: It covers an area of more than 3 million square kilometers. The population exceeds 25 million.
Because the topic says that one of them can be met, we can use or to connect the two conditions.
Solution:
SELECT
name, population, area
FROM
world
WHERE
area >= 3000000 OR population >= 25000000;📒 Method 2 - use union
We can make inquiries according to the two conditions required by the topic.
Condition 1: the area is more than 3 million square kilometers
SELECT name, population, area FROM world WHERE area > 3000000;
Condition 2: population over 25 million
SELECT name, population, area FROM world WHERE population > 25000000;
Then we connect the two queries through the Union (there will be a brief explanation of the Union and union all in the summary of the article's knowledge points. Partners should remember to check the deficiencies and fill the gaps ~)
Solution:
SELECT name, population, area FROM world WHERE area > 3000000 UNION SELECT name, population, area FROM world WHERE population > 25000000;
📕 Code test
📒 Method 1
SELECT
name, population, area
FROM
world
WHERE
area >= 3000000 OR population >= 25000000;Execute code and test
Consistent with the expected results, the test is successful!
📒 Method 2
SELECT name, population, area FROM world WHERE area > 3000000 UNION SELECT name, population, area FROM world WHERE population > 25000000;
Execute code and test
Consistent with the expected results, the test is successful!
📕 Summary of knowledge points
📒 The difference between union and union all
1. union performs union operation on two result sets, excluding duplicate rows, and sorts by default rules at the same time;
2. union all performs union operations on two result sets, including duplicate rows, without sorting;
Expand knowledge:
In the last result set, we can change the sorting method by specifying the Order by clause.
😀 Thank you guys. If you have any questions, please leave a message and ask. Xiaomeng will know everything and say everything!
