The SQL UNION Operator

The SQL UNION operator is used to combine data from multiple tables by eliminating duplicate rows (if any).

To use the UNION operator on multiple tables, all these tables must be union compatible. And they are said to be union compatible if and only if they meet the following criteria −

• The same number of columns selected with the same datatype.
• These columns must also be in the same order.
• They need not have same number of rows.

Once these criterion are met, the UNION operator returns the rows from multiple tables as a resultant table which is void of all duplicate values from these tables.

The column names in the final result set will be based on the column names selected in the first SELECT statement. If you want to use a different name for a column in the final result set, you can use an alias in the SELECT statement.

Syntax

The basic syntax of a UNION operator is as follows −

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
UNION
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition];

Here, the given condition could be any given expression based on your requirement.

UNION on a Single Field

If we want to use UNION to combine the result sets of two or more SELECT statements on a single field, we can simply include that field in the SELECT statement of each query. The UNION operator will automatically remove any duplicate values in the final result set.

When using UNION on a single field, the column names in the result set will be determined by the column name in the first SELECT statement. Therefore, you may need to use an alias in the SELECT statement to ensure that the column name is meaningful for the final result set.

Example

Assume we have created a table with name CUSTOMERS in MySQL database using CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query inserts values into this table using the INSERT statement −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The CUSTOMERS table is as follows −

Now, creating the second table ORDERS using CREATE TABLE statement as shown below −

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE DATETIME NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT INT NOT NULL,
   PRIMARY KEY (OID)
);

Following query inserts values into this table using the INSERT statement −

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000),
(100, ''2009-10-08 00:00:00'', 3, 1500),
(101, ''2009-11-20 00:00:00'', 2, 1560),
(103, ''2008-05-20 00:00:00'', 4, 2060);

The ORDERS table is as follows −

Using the following query, let us combine the SALARY and AMOUNT columns from CUSTOMERS and ORDERS table (since these columns have similar datatypes) −

SELECT SALARY FROM CUSTOMERS UNION SELECT AMOUNT FROM ORDERS;

Output

Output of the above query is as follows −

UNION on Multiple Fields

When we use UNION on multiple fields, the number and order of the fields in each SELECT statement must match. Also, the data types of the fields in each SELECT statement must be compatible for the UNION to work correctly. If the data types are not compatible, you may need to use conversion functions such as CAST or CONVERT to ensure that the data types match.

Example

As the CUSTOMERS and ORDERS tables are not union-compatible individually, let us first join these two tables into a bigger table using Left Join and Right Join. The joined tables retrieved will have same number of columns with same datatypes, becoming union compatible. Now, these tables are combined using UNION query shown below −

SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION
SELECT  ID, NAME, AMOUNT, DATE FROM CUSTOMERS
RIGHT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

This would produce the following result −

UNION with WHERE Clause

We can use the WHERE clause with UNION operator to filter the results of each SELECT statement before combining them.

Syntax

Following is the syntax for using the WHERE clause with UNION operator −

SELECT column1, column2, column3
FROM table1
WHERE column1 = ''value1''
UNION
SELECT column1, column2, column3
FROM table2
WHERE column1 = ''value2

Example

In the following query, we are retrieving the id”s of the customers where id is greater than 5 and 2 from the ”CUSTOMERS” and ”ORDERS” tables respectively −

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2;

Output

Following is the result produced −

UNION with ORDER BY Clause

When we use UNION with ORDER BY clause, it combines the sorted result sets of all SELECT statements and produces a single sorted result set.

Example

In here, we are retrieving the id”s of the customers where id is greater than 5 and 2 from the ”CUSTOMERS” and ”ORDERS” tables respectively, sorted low to high from their salary −

SELECT ID, SALARY FROM CUSTOMERS WHERE ID > 5
UNION
SELECT CUSTOMER_ID, AMOUNT FROM ORDERS WHERE CUSTOMER_ID > 2
ORDER BY SALARY;

Output

Following is the output of the above query −

The ORDER BY clause in a UNION statement applies to the entire result set, not just the last SELECT statement.

UNION with Aliases

We can use aliases in the SELECT statement of UNION operator to give a table or column a temporary name, which can be useful when working with multiple tables or columns with similar names.

When using UNION with aliases, it”s important to note that the column aliases are determined by the first SELECT statement. Therefore, if you want to use different aliases for the same column in different SELECT statements, you need to use column aliases in all SELECT statements to ensure consistent column names in the final result set.

Syntax

Following is the syntax for using Union with Aliases −

SELECT column1 AS alias1, column2 AS alias2
FROM table1
UNION
SELECT column3 AS alias1, column4 AS alias2
FROM table2;

Example

The following query retrieves all the id”s from both tables, along with an indication of whether each id is of the customer or the order made by them −

SELECT ID, ''customer'' AS type FROM CUSTOMERS
UNION
SELECT OID, ''order'' AS type FROM ORDERS;

Output

Following is the output produced −

There are two other operators which are like the UNION operator.

• SQL − This is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

• SQL − This combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.

In a table, columns can typically accept NULL values by default. However, if you want to ensure that a particular column does not contain NULL values, you need to add the NOT NULL constraint/condition on that column.

The SQL NOT NULL Constraint

The NOT NULL constraint in SQL is used to ensure that a column in a table doesn”t contain NULL (empty) values, and prevent any attempts to insert or update rows with NULL values.

Usually, if we don”t provide value to a particular column while inserting data into a table, by default it is considered as a NULL value. But, if we add the NOT NULL constraint on a column, it will enforce that a value must be provided for that column during the data insertion, and attempting to insert a NULL value will result in a constraint violation error.

Syntax

Following is the basic syntax of NOT NULL constraint while creating a table −

CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype,
   column3 datatype NOT NULL,
   ...
);

Creating NOT NULL Constraint On a Table

To add the NOT NULL constraint on a column of a table, we just need to add the keyword “NOT NULL” after the column”s data type in the column definition.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT             NOT NULL,
   NAME VARCHAR (20)  NOT NULL,
   AGE  INT           NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (20, 2),
   PRIMARY KEY (ID)
);

Let”s insert some values into the above created table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', ''32'', ''Ahmedabad'', 2000),
(2, ''Khilan'', ''25'', ''Delhi'', 1500),
(3, ''Kaushik'', ''23'', ''Kota'', 2500),
(4, ''Chaitali'', ''25'', ''Mumbai'', 6500),
(5, ''Hardik'',''27'', ''Bhopal'', 8500),
(6, ''Komal'', ''22'', ''Hyderabad'', 9000),
(7, ''Muffy'', ''24'', ''Indore'', 5500);

The table will be created as shown below −

Verification

To display the structure of a table in MySQL database, we use the DESCRIBE command. The DESCRIBE command provides a summary of the columns, data types, and various attributes of the table as shown below −

DESCRIBE CUSTOMERS;

As we can see in the output below, the table shows information about the column names of the table, their types, and whether they are nullable or not.

Removing a NOT NULL Constraint From the Table

In SQL, to remove a NOT NULL constraint of a column in an existing table, we need to use the ALTER TABLE statement. Using this statement, we can modify the definition of a column i,e you can change the name, data type or constraint of an existing column.

One of a way to remove the NOT NULL constraint on a column is to changing it to NULL.

Syntax

Following is the syntax to remove a not null constraint from the table in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NULL;

Were,

• table_name is the name of the table that contains the columns we want to modify.
• column_name is the name of the column that has the NOT NULL constraint you want to remove.
• datatype is the data type of the column.

Example

Following is the query to modify the constraint on the NAME column of the CUSTOMERS table to NULL in MySQL database −

ALTER TABLE CUSTOMERS MODIFY COLUMN NAME VARCHAR(20) NULL;

Output

On executing the above query, the output is displayed as follows −

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us display the structure of the table named “CUSTOMERS” using the following query −

DESCRIBE CUSTOMERS;

As we can see in the table below, the column “NAME” is modified to nullable, which means NULL values are allowed in this column.

Adding a NOT NULL Constraint to the Existing Table

In the previous section, we have removed the NOT NULL constraint on a column by changing its definition using the ALTER TABLE statement. Similarly, we can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement.

Syntax

Following is the SQL syntax to add the NOT NULL constraint to the existing column in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NOT NULL;

Example

Assume the previously created table CUSTOMERS and let us modify the ADDRESS column ensuring that it does not allow null values using the following query −

ALTER TABLE CUSTOMERS MODIFY COLUMN ADDRESS CHAR(25) NOT NULL;

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

We can display the structure of the CUSTOMERS table using the following query −

DESCRIBE CUSTOMERS;

As we can see in the output below, the column “ADDRESS” is modified, which means NULL values are NOT allowed in this column.

The SQL DELETE is a command of Data Manipulation Language (DML), so it does not delete or modify the table structure but it delete only the data contained within the table. Therefore, any constraints, indexes, or triggers defined in the table will still exist after you delete data from it.

SQL DELETE TABLE Statement

The SQL DELETE TABLE statement is used to delete the existing records from a table in a database. If you wish to delete only the specific number of rows from the table, you can use the WHERE clause with the DELETE statement. If you omit the WHERE clause, all rows in the table will be deleted. The SQL DELETE statement operates on a single table at a time.

Syntax

Following is the basic syntax for using the SQL DELETE command in SQL −

DELETE FROM table_name;

SQL DELETE TABLE with WHERE Clause

We can use the SQL DELETE statement to delete specific rows from a table based on a single condition using the WHERE clause.

Syntax

Following is the syntax for deleting specific rows based on single condition −

DELETE FROM table_name
WHERE condition;

Example

Assume we have creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc.

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

If you retrieve the contents of the above created table using the SELECT * FROM CUSTOMERS statement you will get the following output −

Now, let”s try to delete all the customers with the name ”Hardik” as shown in the query below −

DELETE FROM CUSTOMERS WHERE NAME=''Hardik

Output

We get the following result. We can observe that 1 row has been deleted.

Query OK, 1 row affected (0.05 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

Deleting rows based on multiple conditions

We can also use the SQL DELETE TABLE statement to delete specific rows from a table based on multiple conditions using the WHERE clause. This is useful when we want to remove a subset of rows from a table that meet a certain criterion.

When using multiple conditions, we can use the comparison operators such as AND, OR, and NOT to refine our conditions. This way, only rows that satisfy the conditions will be deleted.

Syntax

Following is the basic syntax for deleting specific rows based on multiple conditions which can be connected using either AND or OR operators −

DELETE FROM table_name
WHERE condition1 AND condition2 OR ... conditionN;

Here, table_name is the name of the table from which we want to delete rows, and condition1 through conditionN are the conditions that must be met for the rows to be deleted. The AND or OR operators can be used to join the conditions together.

Example

In the following query we are trying to delete all the customers whose name is either ”Komal” or their address is ”Mumbai” −

DELETE FROM CUSTOMERS
WHERE NAME=''Komal'' OR ADDRESS=''Mumbai

Output

We get the following result. We can observe that 2 rows has been deleted.

Query OK, 2 rows affected (0.03 sec)

Verification

Now if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS command you will get the following output −

Deleting all the records in a table

We can use the SQL DELETE TABLE statement without a WHERE clause to delete all records in a table in SQL. This statement will remove all the rows from the specified table, effectively resetting the table to its original state (containing only the structure and its constraints).

However, it”s important to note that this operation cannot be undone, and all the data in the table will be permanently deleted.

Example

In here, we are trying to delete all the records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

Following is the result produced by executing the above query −

Query OK, 4 rows affected (0.13 sec)

Verification

Now, if you retrieve the contents of the CUSTOMERS table using the SELECT * FROM CUSTOMERS statement you will get the following result −

Empty set (0.00 sec)

The SQL INSERT INTO Statement

The SQL INSERT INTO Statement is used to add new rows of data into a table in the database. Almost all the RDBMS provide this SQL query to add the records in database tables.

Each value in the records we are inserting in a table using this statement should be of the same datatype as the respective column and satisfy the constraints of the column (if any). The values passed using an insert statement should match the number of columns in the table or, the number of columns mentioned in the current query. If any of these conditions are not satisfied, this statement generates an error.

Syntax

There are two basic syntaxes of the SQL INSERT INTO statement which are shown below −

INSERT INTO TABLE_NAME (column1, column2...columnN)
VALUES (value1, value2...valueN);

Here, column1, column2, column3,…columnN are the names of the columns in the table into which you want to insert the data.

There is another syntax of INSERT INTO statement where you can specify only column values without column names. But, make sure the order of the values is in the same order as the columns in the table.

Following is second syntax of the SQL INSERT Query −

INSERT INTO TABLE_NAME
VALUES (value1,value2...valueN);

Example

To see an example, let us create a table with name CUSTOMERS in the MySQL database using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The following SQL INSERT INTO statements will create three records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, ''Khilan'', 25, ''Delhi'', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, ''Kaushik'', 23, ''Kota'', 2000.00 );

We can also insert multiple rows at once using the following query as shown below −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 );

Following query adds another record in the CUSTOMERS table using the second syntax as shown below −

INSERT INTO CUSTOMERS
VALUES (7, ''Muffy'', 24, ''Indore'', 10000.00 );

Verification

To check if the records are inserted into the CUSTOMERS table, use the SELECT query −

SELECT * FROM CUSTOMERS;

The table will be displayed with all the records included in it.

Inserting Data into a Table Using Another

Sometimes, you just need to copy the data from an existing table to another table in the same database. SQL provides convenient ways to do so −

• Using INSERT… SELECT
• Using INSERT… TABLE

The INSERT… SELECT Statement

You can populate the data into a table through the select statement using an already existing another table; provided the other table has a set of fields, which are required to populate the first table.

Here is the syntax −

INSERT INTO first_table_name [(column_name(s))]
SELECT column1, column2, ...columnN
FROM second_table_name
[WHERE condition];

Example

The following statement would create another table named BUYERS with the same structure as CUSTOMERS table −

CREATE TABLE BUYERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now using the INSERT… INTO statement, let us insert all the records from the CUSTOMERS table into the BUYERS table.

INSERT INTO BUYERS (ID, NAME, AGE, ADDRESS, SALARY)
SELECT * FROM CUSTOMERS;

Output

The output will be displayed as −

Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

To verify if the records are inserted properly or not, use the following SELECT query −

SELECT * FROM BUYERS;

The table will be displayed containing the same records as CUSTOMERS −

The INSERT… TABLE Statement

If you have two tables structure exactly same, then instead of selecting specific columns you can insert the contents of one table into another using the INSERT…TABLE statement.

Following is the syntax to do so −

INSERT INTO first_table_name TABLE second_table_name;

Example

In this example, let us use the same CUSTOMERS table we have created in the previous example and copy its contents into another table named SHOPPERS. For that, let”s create the table SHOPPERS with the same structure as CUSTOMERS table −

CREATE TABLE SHOPPERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now use the following statement to insert all the records from the CUSTOMERS table into SHOPPERS table −

INSERT INTO SHOPPERS TABLE CUSTOMERS;

Output

This query will generate the following output −

Query OK, 7 rows affected (0.13 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

If you verify the contents of the SHOPPERS table using the SELECT statement shown below −

SELECT * FROM SHOPPERS;

The table will be displayed with the newly inserted values as −

Insert Data Only in Specified Columns

You can select only particular columns from a table to insert into another table. The following SQL statement will insert a new record into BUYERS table with “ID”, “NAME” and “AGE” from CUSTOMERS table.

We can skip only fields which are not defined as NOT NULL, but if we have defined a column as NOT NULL, then we need to provide a value to this column otherwise it will raise an error and record will not be inserted.

Before we proceed further let”s clean all the records from BUYERS table as follows:

DELETE FROM BUYERS;

Now we have empty BUYERS table, let”s use the following SQL statement:

INSERT INTO BUYERS (ID, NAME, AGE)
SELECT ID, NAME, AGE FROM CUSTOMERS;

Output

This query will generate the following output −

Query OK, 7 rows affected (0.13 sec)
Records: 7  Duplicates: 0  Warnings: 0

Verification

If you verify the contents of the BUYERS table using the SELECT statement shown below −

SELECT * FROM BUYERS;

The table will be displayed with the newly inserted values as −

The SQL SELECT Statement

The SQL SELECT Statement is used to fetch the data from a database table which returns this data in the form of a table. These tables are called result-sets.

CLAUSES and OPERATORS available in SQL can be used with the SELECT statement in order to retrieve the filtered records of a database table.

Syntax

The basic syntax of the SELECT Query is as follows −

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2… are the fields of a table whose values you want to fetch. If you want to fetch all the columns available in a table, then you can use the following syntax −

SELECT * FROM table_name;

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as −

Retrieving Selected Fields/Columns

The following statement fetches the ID, Name and Salary fields of the records available in CUSTOMERS table.

SELECT ID, NAME, SALARY FROM CUSTOMERS;

Output

The above query would produce the following table −

Retrieving All Fields/Columns

If you want to fetch all the fields of the CUSTOMERS table, then you should use the query of SELECT statement with an Asterisk (*) instead of the column names, as shown below −

SELECT * FROM CUSTOMERS;

Output

The resultant table will be −

Computing Using SELECT

The SQL SELECT statement can also be used to retrieve the results of various mathematical computations in the form of a table. In such cases, you do not need to specify any database table in the statement.

Following is the syntax to do so −

SELECT mathematical_expression;

Example

Following is an example which multiply two given numbers using SQL statement.

SELECT 56*65;

The query above produces the following output −

Aliasing a Column in SELECT Statement

Whenever a column name in a table is too difficult to read and understand, SQL provides a method to alias this column name into another understandable and relative name. This is done using the AS keyword. You can use the AS keyword in a SELECT statement to display the column names of a table as an alias name.

Following is the syntax to do so −

SELECT column_name
AS alias_name
FROM table_name;

You can also use an alias to display SELECT expressions with the same syntax; you should use a mathematical statement instead of column_name.

Example

In the example below, we are trying to retrieve customer details NAME and AGE in a single column of the resultant table using the concat() expression and aliasing the column as DETAILS along with the customer addresses from the CUSTOMERS table. This will be done using SELECT statement in the following query −

SELECT CONCAT(NAME,'' '',AGE)
AS DETAILS, ADDRESS
FROM CUSTOMERS ORDER BY NAME;

The query above produces the following output −

The SQL Select Into Statement

The SQL SELECT INTO Statement creates a new table and inserts data from an existing table into the newly created table. The new table is automatically created based on the structure of the columns in the SELECT statement and can be created in the same database or in a different database.

However, it”s important to note that the SELECT INTO statement does not preserve any indexes, constraints, or other properties of the original table, and the new table will not have any primary keys or foreign keys defined by default. Therefore, you may need to add these properties to the new table manually if necessary.

MySQL doesn”t support the SELECT … INTO TABLE Sybase SQL extension i.e. in MySQL you cannot use the SELECT … INTO statement to insert data from one table to another. Instead of this, we can use INSERT INTO … SELECT statement or, CREATE TABLE … SELECT.

Syntax

Following is the basic syntax of the SQL SELECT INTO statement in SQL Server −

SELECT * INTO new_table_name FROM existing_table_name

Example

Let us create the CUSTOMERS table which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table will be creates as follows −

The following SELECT INTO statement creates a new table called CUSTOMER_BACKUP and copies the data from the CUSTOMERS table into it −

SELECT * INTO CUSTOMER_BACKUP FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_BACKUP table −

SELECT * from CUSTOMER_BACKUP;

The table displayed is as follows −

Copying Data From Specific Columns

We can also copy data from specific columns from an existing table into the new table using the SQL SELECT INTO statement. To do so, we just need to include the required column names after the select keyword.

Syntax

Following is the syntax −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM existing_table_name;

Example

In the following query, we are creating a new table called CUSTOMER_DETAILS with only the NAME, AGE, and ADDRESS columns from the CUSTOMERS table, and populate it with the corresponding data.

SELECT name, age, address
INTO CUSTOMER_DETAILS
FROM CUSTOMERS;

Output

We get the following result. We can observe that 7 rows have been modified.

(7 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_DETAILS table −

SELECT * from CUSTOMER_DETAILS;

The table displayed is as follows −

Note: The new table will not include any other columns from the original table. Also the original table remains unchanged.

Copying Data From Multiple Tables

Using the SQL SELECT INTO statement we can also copy data from multiple tables to a new table. This is accomplished using the JOIN clause which combines the data from multiple tables (based on a common column).

Syntax

Following is the syntax to copy data from multiple tables using the SELECT INTO statement −

SELECT column1, column2, ..., columnN
INTO new_table_name
FROM table1
JOIN table2 ON table1.column = table2.column

Example

First of all, let us create another table named ORDERS −

CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2));

Using the INSERT statement, insert values into this table as follows −

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000.00),
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00),
(103, ''2008-05-20 00:00:00'', 4, 2060.00);

The table is created as −

Now, we are creating a new table called CUSTOMER_ORDERS that includes the customer name from the CUSTOMERS table and the customer id from the ORDERS table, where the id of customers from the CUSTOMERS table matches with the id of customers from the ORDERS table −

SELECT CUSTOMERS.Name, ORDERS.customer_id
INTO CUSTOMER_ORDERS
FROM CUSTOMERS
LEFT JOIN ORDERS ON CUSTOMERS.ID = ORDERS.customer_id;

Output

We get the following result. We can observe that 8 rows have been modified.

(8 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the CUSTOMER_ORDERS table −

SELECT * FROM CUSTOMER_ORDERS;

The table displayed is as follows −

Copying Specific Records

We can also use the SQL SELECT INTO statement with a WHERE clause to create a new table and copy specific rows from an existing table into it.

Syntax

Following is the syntax for using SELECT INTO statement with a WHERE clause −

SELECT *
INTO new_table_name
FROM existing_table_name
WHERE condition;

Example

Using the following query we are creating a new table called NameStartsWith_K that includes all columns from the CUSTOMERS table, but it only stores the records of the customers whose name starts with “k”.

SELECT *
INTO NameStartsWith_K
FROM CUSTOMERS
WHERE NAME LIKE ''k%

Output

We get the following result. We can observe that 3 rows have been modified.

(3 rows affected)

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement.

SELECT * from NameStartsWith_K;

The table displayed is as follows −

The Insert Into… Select Statement

The SQL INSERT INTO… SELECT statement is used to add/insert one or more new rows from an existing table to another table. This statement is a combination of two different statements: INSERT INTO and SELECT.

• The INSERT INTO statement is one of the most fundamental and frequently used statements in database management and requires only the name of the table and the values to be inserted. However, it is important to ensure that the data being inserted satisfies the constraints if the columns of a table (if any) and its type matches the data types of the table columns.

• The SELECT statement is used to retrieve data from an existing database table.

When these statements are used together, the SELECT statement first retrieves the data from an existing table and the INSERT INTO statement inserts the retrieved data into another table (if they have same table structures).

Syntax

Following is the syntax of the SQL INSERT INTO… SELECT statement −

INSERT INTO table_new
SELECT (column1, column2, ...columnN)
FROM table_old;

Before using this query, we have to make sure that −

• In the database where we are going to insert data, source and target tables already exist.

• The structure of the source and target tables are same.

Example

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc.., as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(2, ''Khilan'', 25, ''Delhi'', 1500.00),
(3, ''Kaushik'', 23, ''Kota'', 2000.00),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00),
(7, ''Muffy'', 24, ''Indore'', 10000.00);

The table will be created as −

Create another table named BUYERS with same structure as the CUSTOMERS table.

CREATE TABLE BUYERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query copies all the records from the CUSTOMERS table to BUYERS −

INSERT INTO BUYERS SELECT * FROM CUSTOMERS;

Verification

If you verify the contents of the BUYERS table using the SELECT statement as −

SELECT * FROM BUYERS;

The table will be created as −

SQL – Inserting Specific Records

Sometimes we only need to add a small number of records to another table. This can be accomplished by using a WHERE clause along with the SQL INSERT INTO… SELECT statement.

Example

Let us create a table named NAMESTARTSWITH_K with the same structure as the CUSTOMER table using the CREATE statement as −

CREATE TABLE NAMESTARTSWITH_K (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query inserts the records of the customers whose name starts with the letter k from the CUSTOMERS table to the BUYERS table −

INSERT INTO NAMESTARTSWITH_K
SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''k%

Verification

Following is the SELECT statement to verify the contents of the above created table −

SELECT * FROM NAMESTARTSWITH_K;

The table will be created as −

SQL – Inserting Top N Rows

The LIMIT clause filters the number of rows from the query. You can use this to filter the top N records that should be added to the target table.

Example

But, before proceeding further, let us truncate all rows in the BUYERS table using the following statement −

TRUNCATE TABLE BUYERS;

Following query inserts the top 3 records from the CUSTOMERS table to the BUYERS table −

INSERT INTO BUYERS
SELECT * FROM CUSTOMERS
ORDER BY ID ASC LIMIT 3;

Verification

Let us verify the contents of the BUYERS table −

SELECT * FROM BUYERS;

The resultant table will be as follows −

The SQL DELETE Statement

The SQL DELETE Statement is used to delete the records from an existing table. In order to filter the records to be deleted (or, delete particular records), we need to use the WHERE clause along with the DELETE statement.

If you execute DELETE statement without a WHERE clause, it will delete all the records from the table.

Using the DELETE statement, we can delete one or more rows of a single table and records across multiple tables.

Syntax

The basic syntax of the SQL DELETE Query with the WHERE clause is as follows −

DELETE FROM table_name WHERE [condition];

You can combine N number of conditions using AND or OR operators.

Example

Assume we have created a table named CUSTOMERS which contains the personal details of customers including their name, age, address and salary etc. as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as −

The following query deletes the record of a customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

Output

The output will be displayed as −

Query OK, 1 row affected (0.10 sec)

Verification

To verify whether the record(s) have been deleted from the table, we need to retrieve the modified table using the SELECT query, as shown below −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would have the following records −

Deleting Multiple Rows

To delete multiple rows from a table, we need to specify the required condition(s), that is satisfied by all the rows to be deleted, using the WHERE clause. Let us look at an example −

Example

From the same Customers table, let us try to delete the records of customers who are over 25 years of age.

DELETE FROM CUSTOMERS WHERE AGE > 25;

Output

The output will be displayed as −

Query OK, 2 rows affected (0.06 sec)

Verification

To verify whether the record(s) have been deleted from the table or not, let us retrieve the modified table. For that, use the SELECT query below −

SELECT * FROM CUSTOMERS;

The query above will produce the following table −

Deleting All The Records From a Table

If we want to DELETE all the records from an existing table (truncate it) using the DELETE query, we simply need to run it without using the WHERE clause.

Example

Following SQL query removes all the records from the CUSTOMERS table −

DELETE FROM CUSTOMERS;

Output

The output will be displayed as −

Query OK, 4 rows affected (0.13 sec)

Verification

To verify whether all the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record and will show the following output −

Empty set (0.00 sec)

Delete Records in Multiple Tables

SQL allows us to delete the records from multiple tables using the DELETE query. In here, we will use the JOIN clause to combine data from multiple tables (based on a common column).

Example

Let us create another table with name ORDERS which contains the details of the orders made by the customers.

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATE VARCHAR (20) NOT NULL,
   CUSTOMER_ID INT NOT NULL,
   AMOUNT DECIMAL (18, 2)
);

Using the INSERT statement, insert values into this table as follows

INSERT INTO ORDERS VALUES
(102, ''2009-10-08 00:00:00'', 3, 3000.00),
(100, ''2009-10-08 00:00:00'', 3, 1500.00),
(101, ''2009-11-20 00:00:00'', 2, 1560.00),
(103, ''2008-05-20 00:00:00'', 4, 2060.00);

The table created is as shown below −

Following SQL query deletes the records of the customers (from the tables CUSTOMERS and ORDERS) who earn more than 2000 and have placed orders −

DELETE CUSTOMERS, ORDERS FROM CUSTOMERS
INNER JOIN ORDERS ON ORDERS.CUSTOMER_ID = CUSTOMERS.ID
WHERE CUSTOMERS.SALARY > 2000;

Output

The output will be displayed as −

Query OK, 2 rows affected (0.01 sec)

Verification

To verify whether the records have been deleted from the table, we need to retrieve the modified table once again, using the SELECT query −

SELECT * FROM CUSTOMERS;

The CUSTOMERS table would not have the record(s) where salary is greater than 2000 and the ID matches with the CUSTOMER_ID column in the ORDERS table.

Similarly, if you verify the ORDERS table as shown below −

SELECT * FROM ORDERS;

Since salary is greater than 2000 and the CUSTOMER_ID matches with the ID value in the CUSTOMERS table, the last record (OID 103) of the ORDERS table will be deleted −

The SQL UPDATE Statement

The SQL UPDATE Statement is used to modify the existing records in a table. This statement is a part of Data Manipulation Language (DML), as it only modifies the data present in a table without affecting the table”s structure.

To filter records that needs to be modified, you can use a WHERE clause with UPDATE statement. Using a WHERE clause, you can either update a single row or multiple rows.

Since it only interacts with the data of a table, the SQL UPDATE statement needs to used cautiously. If the rows to be modified aren”t selected properly, all the rows in the table will be affected and the correct table data is either lost or needs to be reinserted.

The SQL UPDATE statement makes use of locks on each row while modifying them in a table, and once the row is modified, the lock is released. Therefore, it can either make changes to a single row or multiple rows with a single query.

Syntax

The basic syntax of the SQL UPDATE statement with a WHERE clause is as follows −

UPDATE table_name
SET column1 = value1, column2 = value2,..., columnN = valueN
WHERE [condition];

You can combine N number of conditions using the AND or the OR operators.

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement as shown below −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The table will be created as −

The following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS = ''Pune'' WHERE ID = 6;

Output

The query produces the following output −

Query OK, 1 row affected (0.13 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE ID=6;

Now, the CUSTOMERS table would have the following records −

Update Multiple ROWS and COLUMNS

Using SQL UPDATE statement, multiple rows and columns in a table can also be updated. To update multiple rows, specify the condition in a WHERE clause such that only the required rows would satisfy it.

However, to update multiple columns, set the new values to all the columns that need to be updated. In this case, using the WHERE clause would narrow down the records of the table and not using the clause would change all the values in these columns.

Syntax

Following is the syntax to update multiple rows and columns −

UPDATE table_name
SET column_name1 = new_value, column_name2 = new_value...
WHERE condition(s)

Example

If you want to modify all the AGE and the SALARY column values in the CUSTOMERS table, you do not need to use the WHERE clause as the UPDATE query would be enough. Following query increases the age of all the customers by 5 years and adds 3000 to all the salary values −

UPDATE CUSTOMERS SET AGE = AGE+5, SALARY = SALARY+3000;

Output

The query produces the following output −

Query OK, 7 rows affected (0.12 sec)
Rows matched: 7  Changed: 7  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS;

Now, CUSTOMERS table would have the following records −

Example

But, if you want to modify the ADDRESS and the SALARY columns of selected records in the CUSTOMERS table, you need to specify a condition to filter the records to be modified, using the WHERE clause, as shown in the following query −

UPDATE CUSTOMERS
SET ADDRESS = ''Pune'', SALARY = 1000.00
WHERE NAME = ''Ramesh

Output

This query produces the following output −

Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify whether the records of the table are modified or not, use the following SELECT query below −

SELECT * FROM CUSTOMERS WHERE NAME = ''Ramesh

Now, CUSTOMERS table would have the following records −

What is SQL View

A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database.

The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to −

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables which can be used to generate reports.

The SQL CREATE VIEW Statement

To create a view in a database, you can use the SQL CREATE VIEW statement.

Syntax

Following is the syntax of the SQL CREATE VIEW statement −

CREATE VIEW view_name AS
SELECT column1, column2....
FROM table_name
WHERE [condition];

Example

Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Following query creates a view based on the above created table −

CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;

Verification

You can verify the contents of a view using the select query as shown below −

SELECT * FROM CUSTOMERS_VIEW;

The view is displayed as follows −

Create View With WHERE Clause

We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below −

CREATE VIEW BUYERS_VIEW as SELECT * FROM CUSTOMERS
WHERE SALARY > 3000;

Verification

Following are the contents of the above created view −

SELECT * FROM BUYERS_VIEW;

The view is displayed as follows −

The WITH CHECK OPTION Clause

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause.

If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause.

CREATE VIEW MY_VIEW AS
SELECT name, age
FROM  CUSTOMERS
WHERE age >= 25
WITH CHECK OPTION;

The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25.

Verification

Following are the contents of the above created view −

SELECT * FROM MY_VIEW;

The view is displayed as follows −