SQL Union and UNION ALL operator \sql SELECT into statement \sql CREATE DATABASE statement

Source: Internet
Author: User
Tags ming one table sql create database

SQL UNION operator

The UNION operator is used to combine the result set of two or more SELECT statements.

Note that the SELECT statement inside the UNION must have the same number of columns. The column must also have a similar data type. Also, the order of the columns in each SELECT statement must be the same.

SQL UNION Syntax
SELECT column_name (s) from Table_name1unionselect column_name (s) from table_name2

Note: By default, the UNION operator chooses a different value. If duplicate values are allowed, use UNION all.

SQL UNION All syntax
SELECT column_name (s) from Table_name1union Allselect column_name (s) from table_name2

In addition, the column name in the union result set is always equal to the column name in the first SELECT statement in the Union.

The original table used in the following example: Employees_china:
e_id E_name
01 Zhang, Hua
02 Wang, Wei
03 Carter, Thomas.
04 Yang, Ming
Employees_usa:
e_id E_name
01 Adams, John.
02 Bush, George.
03 Carter, Thomas.
04 Gates, Bill.
Using the UNION command instance

List all the different employee names in China and the United States:

Select E_name from Employees_china UNION select E_name from Employees_usa
Results
E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Gates, Bill.

Note: This command cannot list all employees in China and the United States. In the above example, we have two employees of the same name, and only one of them is listed. The UNION command will only pick a different value.

UNION All

The union ALL command is almost equivalent to the Union command, but the union ALL command lists all values.

SQL Statement 1UNION allsql Statement 2
Use the UNION all command instance:

List all employees in China and the United States:

Select E_name from Employees_china UNION ALL select E_name from Employees_usa
Results
E_name
Zhang, Hua
Wang, Wei
Carter, Thomas.
Yang, Ming
Adams, John.
Bush, George.
Carter, Thomas.
Gates, Bill.
SELECT into statement

The SELECT INTO statement selects data from one table and then inserts the data into another table.

The SELECT into statement is commonly used to create a backup copy of a table or to archive records.

SQL SELECT into syntax

You can insert all the columns into the new table:

SELECT *into new_table_name [in Externaldatabase] from Old_tablename

Or just insert the desired column into the new table:

SELECT column_name (s) to new_table_name [in Externaldatabase] from Old_tablename
SQL SELECT into instance-make backup copy

The following example makes a backup copy of the "Persons" table:

SELECT* INTO Persons_backupfrom Persons

The IN clause can be used to copy a table to another database:

SELECT* INTO IN ' Backup.mdb ' from Persons

If we want to copy some of the fields, we can list them after the SELECT statement:

SELECTLastname,firstname INTO Persons_backupfrom Persons
SQL SELECT into instance-with a WHERE clause

We can also add a WHERE clause.

The following example creates a table named "Persons_backup" with two columns by extracting information from the "Persons" table of people residing in "Beijing":

SELECTLastname,firstname INTO persons_backupfrom Persons WHERE city= ' Beijing '
SQL SELECT into instance-connected table

It is also possible to select data from more than one table.

The following example creates a new table named "Persons_order_backup" that contains the information obtained from the Persons and Orders two tables:

SELECTPersons.lastname,orders.orderno INTO persons_order_backup FROM Persons INNER JOIN Orders ON persons.id_p=orders.id_p
CREATE DATABASE Statement

Create database is used for creating databases.

SQL CREATE DATABASE Syntax
CREATE DATABASE database_name
SQL CREATE DATABASE Instance

Now we want to create a database named "my_db".

We use the following CREATE DATABASE statement:

CREATE DATABASE my_db

SQL Union and UNION ALL operator \sql SELECT into statement \sql CREATE DATABASE statement

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.