MySQL Union Merge Query Data and Analysis of Table Alias and Field Alias Usage

This article describes the example of MySQL Union merged query data and table alias, field alias usage. Share it for your reference, as follows:

union Keyword

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);

Alias a table

SELECT * from orders AS o
WHERE o.o_num = 30001;

SELECT c.c_id, o.o_num
FROM customers AS c LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;

Alias a field

SELECT f1.f_name AS fruit_name, f1.f_price AS fruit_price
FROM fruits AS f1
WHERE f1.f_price < 8;

SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')
FROM suppliers
ORDER BY s_name;

SELECT CONCAT(RTRIM(s_name) , ' (', RTRIM(s_city), ')')
as suppliers_title
FROM suppliers
ORDER BY s_name;

[Example. 62] Query a list of all fruits with a price less than 9, query all fruit types with s_id equal to 101 and 103, and use UNION Join query results

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);

[Example. 63] Query a list of all fruits with a price less than 9, query all fruit types with s_id equal to 101 and 103, and use UNION ALL To join the query results, the SQL statement is as follows

SELECT s_id, f_name, f_price
FROM fruits
WHERE f_price < 9.0
UNION ALL
SELECT s_id, f_name, f_price
FROM fruits
WHERE s_id IN(101,103);

"Example. 64" takes the alias o for the orders table, and queries the placing date of the 30001 order

SELECT * from orders AS o
WHERE o.o_num = 30001;

"Example. 65" aliases the customers and orders tables, respectively, and performs join queries

SELECT c.c_id, o.o_num
FROM customers AS c LEFT OUTER JOIN orders AS o
ON c.c_id = o.c_id;

[Example. 66] Query the fruits table, take the alias fruit_name for f_name, take the alias fruit_price for f_price, take the alias f1 for fruits table, and look up f_price in the table < The name of the fruit in 8

SELECT * from orders AS o
WHERE o.o_num = 30001;

0

"Example. 67" Queries the fields s_name and s_city in the suppliers table, using the CONCAT Function concatenates the two field values and takes the column alias suppliers_title

If you don't alias the connected value, the display column name will not be intuitive enough. Enter the following SQL

SELECT * from orders AS o
WHERE o.o_num = 30001;

1

For more readers interested in MySQL related content, please check out the topics on this site: "MySQL Common Function Summary", "MySQL Log Operation Skills Collection", "MySQL Transaction Operation Skills Collection", "MySQL Stored Procedure Skills Collection" and "MySQL Database Lock Related Skills Collection"

I hope this article is helpful to everyone's MySQL database.