Page 103 - PowerPoint Presentation

CAVITE STATE UNIVERSITY
T3 CAMPUS
Department of Information Technology DCIT 55 – Advance Database System

SQL IN Operator
The IN operator is used in WHERE condition with SELECT, UPDATE and DELETE
statements.
The IN operator returns value that matches values in a list or subquery.
The IN operator is a shorthand for multiple OR conditions.
Syntax: SELECT*FROM table_name WHERE column_name IN (value1, value2, ..);

Example: Consider the ‘Persons’ table having the following records.
ID PersonName Age City
1 Christian 25 Tanza
2 John Mark 23 Trece
3 Nigel 21 Indang
The following SQL statement selects all the persons from ‘Persons’ table who are staying in
either ‘Tanza’ or ‘Trece’.
SELECT*FROM Persons WHERE City IN (‘Tanza’, ‘Trece’);
ID PersonName Age City
1 Christian 25 Tanza
2 John Mark 23 Trece
The above SQL statement using IN operator is equivalent to the following SQL statement
(using OR operator).
SELECT*FROM Persons WHERE City = ‘Tanza’ OR City = ‘Trece’;

SQL NOT IN Operator
The NOT IN operator is used in WHERE condition with SELECT, UPDATE and DELETE
statements.
The NOT IN operator returns values that do not matches values in a list or subquery.
Syntax: SELECT*FROM table_name WHERE column_name NOT IN(value1, value2,…);

Example: Consider the ‘Persons’ table having the following records.
ID PersonName Age City
1 Christian 25 Tanza
2 John Mark 23 Trece
3 Nigel 21 Indang
SELECT*FROM Persons WHERE City NOT IN (‘Indang’);
The above query would produce the following result:
ID PersonName Age City
1 Christian 25 Tanza
2 John Mark 23 Trece

In SQL NULL values represent missing unknown data. By default, a table column can hold
NULL values.
A field with a NULL value is a field with no value. NULL value is different than a zero value or
a field that contains space.
Syntax:
SELECT*FROM table_name WHERE column_name IS NULL;

You can also use NULL value with WHERE condition in UPDATE and DELETE statement.

SQL SET NULL Value (In UPDATE Statement)
Syntax:
UPDATE table_name SET column_name = NULL WHERE column_name = condition;

Page | 19