55. IN Function, INSERT Statement, And INTERSECT Query

SQL: "IN" Function

---

The IN function helps reduce the need to use multiple OR conditions.

The syntax for the IN function is:

SELECT columns
FROM tables
WHERE column1 in (value1, value2, .... value_n);

This SQL statement will return the records where column1 is value1, value2..., or value_n. The IN function can be used in any valid SQL statement - select, insert, update, or delete.

Example #1:

The following is an SQL statement that uses the IN function:

SELECT *
FROM suppliers
WHERE supplier_name in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

It is equivalent to the following statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the IN function makes the statement easier to read and more efficient.

Example #2:

You can also use the IN function with numeric values.

SELECT *
FROM orders
WHERE order_id in (10000, 10001, 10003, 10005);

This SQL statement would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

It is equivalent to the following statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

Example #3 using "NOT IN"

The IN function can also be combined with the NOT operator.

For Example:

SELECT *
FROM suppliers
WHERE supplier_name not in ( 'IBM', 'Hewlett Packard', 'Microsoft');

This would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SQL: INSERT Statement

---

The INSERT statement allows you to insert a single record or multiple records into a table.

The syntax for the INSERT statement is:

INSERT INTO table
(column-1, column-2, ... column-n)
VALUES
(value-1, value-2, ... value-n);

Example #1 - Simple example

Let's take a look at a very simple example.

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(24553, 'IBM');

This would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 24553 and a supplier_name of IBM.

Example #2 - More complex example

You can also perform more complicated inserts using sub-selects.

For Example:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'Newark';

By placing a "select" in the insert statement, you can perform multiples inserts quickly.
With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following SQL statement before performing the insert.

SELECT count(*)
FROM customers
WHERE city = 'Newark';

Frequently Asked Questions

---

Question:  I am setting up a database with clients. I know that you use the "insert" statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer:  You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE not exists (select * from clients
where clients.client_id = suppliers.supplier_id);

This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE not exists (select * from clients
where clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

Learn more about the EXISTS condition.

---

Question:  How can I insert multiple rows of explicit data in one SQL command in Oracle?

Answer:  The following is an example of how you might insert 3 rows into the suppliers table in Oracle.

INSERT ALL
   INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
   INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
   INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

SQL: INTERSECT Query

---

The INTERSECT query allows you to return the results of 2 or more "select" queries. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SQL statement within the INTERSECT query must have the same number of fields in the result sets with similar data types.

The syntax for an INTERSECT query is:
select field1, field2, . field_n
from tables
INTERSECT
select field1, field2, . field_n
from tables;

Example #1

The following is an example of an INTERSECT query:
select supplier_id
from suppliers
INTERSECT
select supplier_id
from orders;

In this example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

Example #2 - With ORDER BY Clause

The following is an INTERSECT query that uses an ORDER BY clause:
select supplier_id, supplier_name
from suppliers
where supplier_id > 2000
INTERSECT
select company_id, company_name
from companies
where company_id > 1000
ORDER BY 2;

Since the column names are different between the two "select" statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".


The supplier_name / company_name fields are in position #2 in the result set.