To update the data entered in a single database table using SQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we need to use the UPDATE… JOIN clause.

For instance, if a student changes their primary phone number and wishes to update it in their organizational database, the information needs to be modified in multiple tables like student records, laboratory records, canteen passes etc. Using the JOIN clause, you can combine all these tables into one, and then using UPDATE statement, you can update the student data in them simultaneously.

The SQL UPDATE… JOIN Clause

The UPDATE statement only modifies the data in a single table and JOINS in SQL are used to fetch the combination of rows from multiple tables, with respect to a matching field.

If we want to update data in multiple tables, we can combine multiple tables into one using JOINS and then update them using UPDATE statement. This is also known as cross-table modification.

Syntax

Following is the basic syntax of the SQL UPDATE… JOIN statement −

UPDATE table(s)
JOIN table2 ON table1.join_column = table2.join_column
SET table1.column1 = table2.new_value1,
    table1.column2 = table2.new_value2;

Where, JOIN can be: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc.

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., 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 );

The table will be created as −

Let us create another table ORDERS, containing the details of orders made and the date they are made on.

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 displayed as follows −

Following UPDATE… JOIN query increments the salary of customers by 1000 with respect to the inflation of their order amount by 500 −

UPDATE CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000,
ORDERS.AMOUNT = ORDERS.AMOUNT + 500;

Verification

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

SELECT * FROM CUSTOMERS;

The updated CUSTOMERS table is displayed as follows −

Now, check whether the ORDERS table is updated using the following SELECT statement −

SELECT * FROM ORDERS;

The updated ORDERS table is displayed as follows −

UPDATE… JOIN with WHERE Clause

While updating records from multiple tables, if we use the WHERE clause along with the UPDATE… JOIN statement we can filter the records to be updated (from the combined result set).

Syntax

The syntax of SQL UPDATE… JOIN with WHERE clause in MySQL database is as follows −

UPDATE table(s)
JOIN table2 ON column3 = column4
SET table1.column1 = value1, table1.column2 = value2, ...
WHERE condition;

Example

Now, let us execute the following query to increase the salary of customer whose id is 3 −

UPDATE CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000
WHERE ORDERS.CUSTOMER_ID = 3;

Verification

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

SELECT * FROM CUSTOMERS;

As we can see in the table below, SALARY value of “Kaushik” is increased by 1000 −

The UPDATE… JOIN Clause in SQL Server

The SQL UPDATE… JOIN Clause also works in SQL Server database. But, the syntax of the query is slightly different from that of MySQL. However, the working of it is exactly the same as MySQL query.

In MySQL, the UPDATE statement is followed by the JOIN clause and SET statements respectively. Whereas, in MS SQL Server the SET statement is followed by the JOIN clause.

Syntax

Following is the syntax of the UPDATE… JOIN in SQL Server −

UPDATE tables(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON table1.join_column = table2.join_column;

Example

In this example, we will update values of the CUSTOMERS and ORDERS table that we created above; using the following UPDATE… JOIN query −

UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Verification

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

SELECT * FROM CUSTOMERS;

The updated CUSTOMERS table is displayed as follows −

SQL provides various relational operators to handle data that is spread across multiple tables in a relational database. Out of them, UNION and JOIN queries are fundamentally used to combine data from multiple tables.

Even though they are both used for the same purpose, i.e. to combine tables, there are many differences between the working of these operators. The major difference is that the UNION operator combines data from multiple similar tables irrespective of the data relativity, whereas, the JOIN operator is only used to combine relative data from multiple tables.

Working of UNION

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It does nothing more than just combining information from multiple tables that are union compatible.

The tables are said to be union compatible if they follow the conditions given below −

• The tables to be combined must have same number of columns with the same datatype.
• The number of rows need not be same.

Once these criteria are met, UNION operator returns all the rows from multiple tables, after eliminating duplicate rows, as a resultant table.

Note − Column names of first table will become column names of resultant table, and contents of second table will be merged into resultant columns of same data type.

Syntax

Following is the syntax of the SQL UNION operator −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two table “COURSES_PICKED” and “EXTRA_COURSES_PICKED” with the same number of columns having same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

Create table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the EXTRA_COURSES_PICKED table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''PHOTOGRAPHY'');

Now, let us combine the tables COURSES_PICKED and EXTRA_COURSES_PICKED, using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is −

Working of JOIN

The Join operation is used to combine information from multiple related tables into one, based on their common fields. This operation can be used with various clauses like ON, WHERE, ORDER BY, GROUP BY etc.

There are two types of Joins −

• Inner Join
• Outer Join

The basic type of join is an Inner Join, which only retrieves the matching values of common columns. It is a default join.

The result table of the Outer join includes both matched and unmatched rows from the first table. It is divided into subtypes like Left Join, Right Join, and Full Join.

Syntax

Following is the basic syntax of a Join operation in SQL −

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Example

In the following example, we will join the same tables we created above, i.e., COURSES_PICKED and EXTRA_COURSES_PICKED, using the query below –

SELECT c.STUDENT_ID, c.STUDENT_NAME, COURSE_NAME, COURSES_PICKED
FROM COURSES_PICKED c
JOIN EXTRA_COURSES_PICKED e
ON c.STUDENT_ID = e.STUDENT_ID;

Output

The resultant table will be displayed as follows −

UNION Vs JOIN

As we saw in the examples given above, the UNION operator is only executable on tables that are union compatible, whereas, the JOIN operator joins two tables that need not be compatible but should be related.

Let us summarize all the difference between these queries below −

The SQL Unique Key

The SQL Unique Key (or, Unique constraint) does not allow duplicate values in a column of a table. It prevents two records from having same values in a column.

Unique Key is just an alternative to the Primary Key; as both Unique and Primary Key constraints ensure uniqueness in a column of the table.

Suppose we have a table named CUSTOMERS to store the customer records in a Bank and if one of the column names is MOBILE_NO then, we can create a UNIQUE constraint on this column to prevent the entry of multiple records with the same mobile number.

Features of Unique Keys

Following is the list of some key features of the Unique Key in an SQL database −

• The unique key is similar to the primary key in a table, but it can accept NULL values, whereas the primary key does not.

• It accepts only one NULL value.

• It cannot have duplicate values.

• It can also be used as a foreign key in another table.

• A table can have more than one Unique column.

Creating SQL Unique Key

You can create a Unique Key on a database table using the UNIQUE keyword in SQL. While creating a database table, specify this SQL keyword along with the column (where this key needs to be defined on).

Syntax

Following is the syntax to create a UNIQUE key constraint on a column in a table −

CREATE TABLE table_name(
   column1 datatype UNIQUE KEY,
   column2 datatype,
   .....
   .....
   columnN datatype
);

Example

Using the following SQL query, we are creating a table named CUSTOMERS with five fields ID, NAME, AGE, ADDRESS, and SALARY in it. Here, we are creating a Unique Key on the ID column.

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

Output

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

Since we have created a UNIQUE constraint on the column named ID, we cannot insert duplicate values in it. Let us verify by inserting the following records with duplicate ID values into the CUSTOMERS table −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(1, ''Khilan'', 25, ''Delhi'', 1500.00 );

On execution, following error is displayed proving that the UNIQUE constraint is indeed defined on the ID column −

ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.ID''

Multiple Unique Keys

We can create one or more Unique Keys on one or more columns in an SQL table.

Syntax

Following is the syntax to create unique key constraints on multiple columns in a table −

CREATE TABLE table_name(
   column1 datatype UNIQUE KEY,
   column2 datatype UNIQUE KEY,
   .....
   .....
   columnN datatype
);

Example

Assume we have created a table with the name CUSTOMERS in the SQL database using CREATE TABLE statement. A Unique key is defined on columns ID and NAME using the UNIQUE keyword as shown below −

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

Output

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.03 sec)

Verification

Since we have created a UNIQUE constraint on the column named ID and NAME, we cannot insert duplicate values in it. Let us verify by inserting duplicate records into the BUYERS table using the following INSERT statement −

INSERT INTO BUYERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(1, ''Rajesh'', 25, ''Delhi'', 1500.00 );

Following error is displayed −

ERROR 1062 (23000): Duplicate entry ''1'' for key ''customers.ID''

In the same way if you try to insert the another record with duplicate value for the column NAME as −

INSERT INTO BUYERS VALUES (2, ''Ramesh'', 36, ''Chennai'', 1700.00 );

Following error is generated −

ERROR 1062 (23000): Duplicate entry ''Ramesh'' for key ''buyers.NAME''

Unique Key on an Existing Column

Until now, we have only seen how to define a Unique Key on a column while creating a new table. But, we can also add a unique key on an existing column of a table. This is done using the ALTER TABLE… ADD CONSTRAINT statement.

Syntax

Following is the syntax to create a unique constraint on existing columns of a table −

ALTER TABLE table_name ADD CONSTRAINT
UNIQUE_KEY_NAME UNIQUE (column_name);

Note − Here the UNIQUE_KEY_NAME is just the name of the UNIQUE KEY. It is optional to specify and is used to drop the constraint from the column in a table.

Example

In this example, we add a Unique Key on the ADDRESS column of the existing CUSTOMERS table −

ALTER TABLE CUSTOMERS ADD CONSTRAINT
UNIQUE_ADDRESS UNIQUE(ADDRESS);

Output

Following is the output of the above statement −

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

Dropping an SQL Unique Key

If you have already created a unique key on a column, you can drop it whenever it is not needed. To drop the Unique Key from the column of a table, you need to use the ALTER TABLE statement.

Syntax

Following is the SQL query to drop the UNIQUE constraint from the column of a table −

ALTER TABLE table_name DROP CONSTRAINT UNIQUE_KEY_NAME;

Example

Consider the CUSTOMERS table created above, we have created the UNIQUE constraints on three columns named ID, NAME and ADDRESS; drop the UNIQUE constraints from the column ADDRESS by executing the following SQL query −

ALTER TABLE CUSTOMERS DROP CONSTRAINT UNIQUE_ADDRESS;

Output

Following is the output of the above statement −

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

Verification

Now, let us insert two duplicate records of column ADDRESS −

INSERT INTO CUSTOMERS VALUES
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Ahmedabad'', 1500.00 );

If you verify the contents of the table, you can observe that both the records have the same ADDRESS as shown below −

UNION and UNION ALL operators are just the SQL implementation of algebraic set operators. Both of them are used to retrieve the rows from multiple tables and return them as one single table. The difference between these two operators is that UNION only returns distinct rows while UNION ALL returns all the rows present in the tables.

However, for these operators to work on these tables, they need to follow the conditions given below −

• The tables to be combined must have the same number of columns with the same datatype.
• The number of rows need not be the same.

Once these criterion are met, UNION or UNION ALL operator returns the rows from multiple tables as a resultant table.

Column names of first table will become column names of the resultant table, and contents of second table will be merged into resultant columns of same data type.

What is UNION?

UNION is a type of operator/clause in SQL, that works similar to the union operator in relational algebra. It just combines the information from multiple tables that are union compatible.

Only distinct rows from the tables are added to the resultant table, as UNION automatically eliminates all the duplicate records.

Syntax

Following is the syntax of UNION operator in SQL −

SELECT * FROM table1
UNION
SELECT * FROM table2;

Example

Let us first create two tables COURSES_PICKED and EXTRA_COURSES_PICKED with the same number of columns having the same data types.

Create table COURSES_PICKED using the following query −

CREATE TABLE COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   COURSE_NAME VARCHAR(30) NOT NULL
);

Insert values into the COURSES_PICKED table with the help of the query given below −

INSERT INTO COURSES_PICKED VALUES
(1, ''JOHN'', ''ENGLISH''),
(2, ''ROBERT'', ''COMPUTER SCIENCE''),
(3, ''SASHA'', ''COMMUNICATIONS''),
(4, ''JULIAN'', ''MATHEMATICS'');

The table will be displayed as shown below −

Now, let us create another table EXTRA_COURSES_PICKED using the following query −

CREATE TABLE EXTRA_COURSES_PICKED(
   STUDENT_ID INT NOT NULL,
   STUDENT_NAME VARCHAR(30) NOT NULL,
   EXTRA_COURSE_NAME VARCHAR(30) NOT NULL
);

Following is the query to insert values into the “EXTRA_COURSES_PICKED” table −

INSERT INTO EXTRA_COURSES_PICKED VALUES
(1, ''JOHN'', ''PHYSICAL EDUCATION''),
(2, ''ROBERT'', ''GYM''),
(3, ''SASHA'', ''FILM''),
(4, ''JULIAN'', ''MATHEMATICS'');

The table will be created as shown below −

Now, let us combine both of these tables using the UNION query as follows −

SELECT * FROM COURSES_PICKED
UNION
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table obtained after performing the UNION operation is as follows −

What is UNION ALL?

UNION ALL is also an operator/clause in SQL, that is used to combine multiple tables into one table. However, this operator also preserves the duplicate rows in the resultant tables.

Suppose there are two tables, one of which contains the number of games a player competed in internationally and the other contains the number of games a player played nationally.

As we can see in the tables above, Kohli played 234 matches internationally and 234 matches nationally. Even though the data in these columns is the same, they are all separate matches. There is a need to include both rows in the resultant table displaying the total matches played by a player. So, we use the UNION ALL operator in such cases.

Syntax

Following is the syntax of UNION ALL operator in SQL −

SELECT * FROM table1
UNION ALL
SELECT * FROM table2;

Example

In the following example, let us perform UNION ALL operation on the same sample tables given above: “COURSES_PICKED” and “EXTRA_COURSES_PICKED”, using the given query below −

SELECT * FROM COURSES_PICKED
UNION ALL
SELECT * FROM EXTRA_COURSES_PICKED;

Output

The resultant table is displayed as follows −

In mathematical set theory, the intersection of two sets is a collection of values that are common to both sets.

In real-time scenarios, there will be a huge number of tables in a database that contains information. The user may find it challenging to gather common information from various tables. So we use the INTERSECT operator to accomplish that. It helps to retrieve the common data from various tables.

The SQL INTERSECT Operator

The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two or more tables.

Let us consider the below tables as an example to get a better understanding −

If we perform the intersection operation on both tables described above using the INTERSECT operator, it returns the common records which are Dev and Aarohi.

MySQL database does not support the INTERSECT operator. Instead of this, we can use the DISTINCT operator along with the INNER JOIN clause to retrieve common records from two or more tables.

Syntax

Following is the SQL syntax of INTERSECT operator in Microsoft SQL Server −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

There are some mandatory rules for INTERSECT operations such as the number of columns, data types, and other columns must be the same in both SELECT statements for the INTERSECT operator to work correctly.

Example

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

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

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

INSERT INTO STUDENTS VALUES
(1, ''Naina'', ''Maths'', 24, ''Cricket''),
(2, ''Varun'', ''Physics'', 26, ''Football''),
(3, ''Dev'', ''Maths'', 23, ''Cricket''),
(4, ''Priya'', ''Physics'', 25, ''Cricket''),
(5, ''Aditya'', ''Chemistry'', 21, ''Cricket''),
(6, ''Kalyan'', ''Maths'', 30, ''Football'');

The table produced is as shown below −

Now, let us create another table named STUDENTS_HOBBY using the following query −

CREATE TABLE STUDENTS_HOBBY(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

Once the table is created, let us insert some values to the table using the query below −

INSERT INTO STUDENTS_HOBBY VALUES
(1, ''Vijay'', ''Cricket'', 18),
(2, ''Varun'', ''Football'', 26),
(3, ''Surya'', ''Cricket'', 19),
(4, ''Karthik'', ''Cricket'', 25),
(5, ''Sunny'', ''Football'', 26),
(6, ''Dev'', ''Cricket'', 23);

The table created is as follows −

Now, we are retrieving the common records from both the tables using the following query −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS;

Output

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

INTERSECT with BETWEEN Operator

We can use the INTERSECT operator with the BETWEEN operator in SQL to find records that fall within a specified range.

Example

Now, let us retrieve the name, age, and hobby of students aged between 25 and 30 from both the ”STUDENTS” and ”STUDENTS_HOBBY” tables, returning only the common rows within the specified age range −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30;

Output

The output for the above query is produced as given below −

INTERSECT with IN Operator

We can also use the INTERSECT operator with the IN operator in SQL to find the common records that exists in the specified list of values. The IN operator is used to filter a result set based on a list of specified values.

Example

The following SQL query returns the name, age, and hobby of students who have ”Cricket” as their hobby in both ”STUDENTS” and ”STUDENTS_HOBBY” tables −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE HOBBY IN(''Cricket'')
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN(''Cricket'');

Output

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

INTERSECT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string. The INTERSECT operator can also be used with the LIKE operator in SQL to find the common rows that matches with the specified pattern.

Example

The query below retrieves the names that start with ”V” using the wildcard ”%” in the LIKE operator from the common names of both tables −

SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY
WHERE NAME LIKE ''v%''
INTERSECT
SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE ''v%

Output

The output for the above query is produced as given below −

Operators are reserved words primarily used in SQL to perform various operations on data, like addition (+), subtraction (-), or comparison (==).

Conjunctive operators, specifically used in boolean logic, combines two conditions in an SQL statement. The most common conjunctive operators are: AND (&&), which returns true if both conditions are true, and OR (||), which returns true if at least one condition is true.

The SQL AND Operator

The SQL AND returns true or 1, if both its operands evaluates to true. We can use it to combine two conditions in the WHERE clause of an SQL statement.

Syntax

The basic syntax of the SQL AND operator with a WHERE clause is as follows −

WHERE [condition1] AND [condition2];

Where, condition1, condition2 are the conditions we want to apply to the query.

You can combine N number of conditions using the AND operator. For an action to be taken by the SQL statement, whether it be a transaction or a query, all the specified conditions (separated by the AND operator) must be TRUE.

Example

Assume we have created a table with name CUSTOMERS in 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)
);

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 table obtained is as shown below −

Following is an example which would fetch the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −

SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 AND AGE < 25;

Output

This would produce the following result −

Multiple AND Operators

You can also use multiple ”AND” operators in an SQL query to combine multiple conditions (or, expressions) together. Conditions combined with the ”AND” operators are evaluated from left to right. If any of the condition evaluate to false, the entire compound condition will be false and the record will not be included in the result set.

Syntax

Following is the syntax −

WHERE [condition1] AND [condition2]...AND [conditionN];

Example

In the following query, we are selecting all records from the CUSTOMERS table where the name of the customer starts with ”K”, the age of the customer is greater than or equal to 22, and their salary is less than 3742 −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''k%'' AND AGE >= 22 AND SALARY < 3742;

Output

Following is the result produced −

AND with Other Logical Operators

The “AND” operator can be used in combination with other logical operators to filter records from a database table.

When using multiple logical operators in SQL, the order of operations is important. Parentheses can be used to control the order of operations and ensure that the conditions are evaluated in the correct order.

Additionally, using too many logical operators or complex expressions can negatively impact query performance, so it”s important to carefully consider the design of the WHERE clause when working with large datasets.

Example

In here, we are combining the AND operator with the NOT operator to create a NAND operation. The ”NAND” operation returns true if at least one of the input conditions is false, and false if both input conditions are true.

In the following query, we are selecting all records from the CUSTOMERS table where the condition (salary is greater than 4500 and the age is less than 26) is false. The “NOT” operator negates the entire condition, and the “AND” operator combines two conditions −

SELECT * FROM CUSTOMERS
WHERE NOT (SALARY > 4500 AND AGE < 26);

Output

Following is the output of the above query −

AND with UPDATE Statement

We can use the AND operator in the WHERE clause of the UPDATE statement to modify the rows in a table that meet certain criteria.

Syntax

Following is the syntax of using the AND operator with the UPDATE statement −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition1 AND condition2 AND ...;

Where, table_name is the name of the table we want to update, column1, column2, etc. are the columns we want to modify, and value1, value2, etc. are the new values we want to set for those columns.

Example

In the following query, we are updating the salary of all the customers whose age is greater than 27 and updating it to ”55000” using UPDATE statement −

UPDATE CUSTOMERS SET SALARY = 55000 WHERE AGE > 27;

Output

We get the following result. We can observe that the salary of 1 customer has been modified −

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

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the salary of ”Ramesh” has been updated to ”55000” because his age is 32 i.e. greater than 27.

The SQL OR Operator

The OR operator returns true if at least one its operands evaluates to true, and false otherwise. We can combine two conditions in an SQL statement”s WHERE clause using the OR operator.

Syntax

The basic syntax of the OR operator with a WHERE clause is as follows −

WHERE [condition1] OR [condition2];

Where, condition1, condition2 are the conditions we want to apply to the query. Each condition is separated by the OR operator.

You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, at least of the conditions separated by the OR operator must be TRUE.

Example

The following query fetches the ID, NAME and SALARY fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years −

SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 OR AGE < 25;

Output

This would produce the following result −

Multiple OR Operators

In SQL, it is common to use multiple OR operators to combine multiple conditions or expressions together. While using multiple OR operators, any rows that meet at least one of the conditions will be included in the result-set.

Example

In the following query, we are selecting all records from the CUSTOMERS table where either the name of the customer ends with ”l”, or the salary of the customer is greater than 10560, or their age is less than 25 −

SELECT * FROM CUSTOMERS
WHERE NAME LIKE ''%l'' OR SALARY > 10560 OR AGE < 25;

Output

Following is the result obtained −

OR with AND Operator

We can also use AND and OR operators together in SQL to combine multiple conditions in a WHERE clause to filter rows that meets the specified criteria.

Syntax

Following is the syntax for using the AND and OR operators together −

WHERE (condition1 OR condition2) AND condition3;

Where, condition1, condition2, and condition3 represent the conditions that we want to combine with the AND and OR operators. The parentheses group the first two conditions and combine them with the OR operator. The result of that operation is combined with the third condition using the AND operator.

Example

In the following query, we are retrieving all rows from the “CUSTOMERS” table where the age of the customer is equal to 25 or the salary is less than 4500 and the name is either Komal or Kaushik. The parentheses control the order of evaluation so that the OR operator is applied first, followed by the AND operator −

SELECT * FROM CUSTOMERS
WHERE (AGE = 25 OR SALARY < 4500)
AND (NAME = ''Komal'' OR NAME = ''Kaushik'');

Output

This would produce the following result −

OR with DELETE Statement

We can also use the OR operator with the DELETE statement to delete rows that meet any one of the (multiple) conditions.

Syntax

Following is the syntax of using OR operator with DELETE statement −

DELETE FROM table_name
WHERE column1 = ''value1'' OR column2 = ''value2

Example

In the following query, we are deleting the records from the CUSTOMERS table where either the age of the customer equals 25 or their salary is less than 2000 −

DELETE FROM CUSTOMERS WHERE AGE = 25 OR SALARY < 2000;

Output

We get the following result −

Query OK, 2 rows affected (0.01 sec)

Verification

To verify if the changes are reflected in the tables, we can use SELECT statement to print the tables. Following is the query to display the records in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

A Boolean is a universal data type which stores true or false values. It is used when we define a variable in a column of the table.

For instance, a customer wants a list of all the red cars. So, we can find this using the BOOLEAN operator as given in the below table −

Here, IS_RED is the BOOLEAN column that returns either TRUE or FALSE values based on the color of the cars.

The databases like PostgreSQL and PL/SQL provides the Boolean data type which is abbreviated as BOOL. Whereas the databases like MySQL and oracle SQL does not have a Boolean data type. To represent Boolean values, they provide TINYINT and BIT data type respectively.

Boolean in MySQL

MySQL provides various options for handling Boolean data. You can use BOOL, BOOLEAN, or TINYINT to represent Boolean values.

When you use BOOL or BOOLEAN, MySQL internally converts them into TINYINT. Similar to many programming languages like PHP, C, and C++, MySQL represents the TRUE literal as 1 and the FALSE literal as 0.

Example

Here, we are creating a table ”CARS” with column BOOLEAN. The query to create a table is as follows −

CREATE TABLE CARS (
   ID INT NOT NULL,
   Name VARCHAR(150),
   IsRed BOOLEAN
);

In the above example, a table is created with a BOOLEAN column IsRed. You can insert TRUE as 1 or FALSE as 0 in this column to represent the corresponding Boolean values.

Boolean in MS SQL Server

In MS SQL Server, there is no direct BOOLEAN data type. Instead, you can use the BIT data type to represent Boolean values, where 0 represents FALSE and 1 represents TRUE. The BIT data type can also accept NULL values.

Example

Following is an example to create a table with a BOOLEAN column in SQL Server −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   Name VARCHAR(150),
   IsAvailable BIT
);

In the example above, a table named CUSTOMERS is created with a BOOLEAN column IsAvailable represented as a BIT data type. You can insert 0 for FALSE, 1 for TRUE, or NULL for an unknown value in this column.

Now that you understand how Boolean data types are implemented in SQL Server and MySQL, let us explore how to handle Boolean data in SQL, including filtering and querying based on Boolean columns.

Filtering Boolean Data

You can filter data based on Boolean columns in SQL. For example, in MySQL, to find all the red cars, you can use the BOOLEAN column ”IsRed” to filter for TRUE values as shown below −

SELECT * FROM CARS WHERE IsRed = TRUE;

In SQL Server, to find cars that are red, you can filter for TRUE values (IsRed = 1) as follows −

SELECT * FROM CARS WHERE IsRed = 1;

Negating Boolean Conditions

You can also negate Boolean conditions to find records that are NOT TRUE. For example, to find cars that are not red, use the following query in MySQL −

SELECT * FROM CARS WHERE IsRed = 0;

Following is the query in SQL Server −

SELECT * FROM CARS WHERE IsRed = FALSE;

Working with NULL Values

You can handle NULL values of Boolean data in SQL as well. As mentioned earlier, the BIT data type in SQL Server and the BOOL/BOOLEAN data types in MySQL can accept NULL values, which can represent unknown or unspecified conditions.

To filter records with NULL values in a Boolean column, you can use the IS NULL or IS NOT NULL condition in both MySQL and SQL Server −

-- Finding cars with unspecified availability
SELECT * FROM CARS WHERE IsAvailable IS NULL;

-- Finding cars with specified availability
SELECT * FROM CARS WHERE IsAvailable IS NOT NULL;

In the queries above, we filter cars based on whether their IsAvailable column is NULL or not NULL.

Updating Boolean Values

You can also update Boolean values in your SQL tables. To change the value of a Boolean column in MySQL, you can use the UPDATE statement as shown below −

-- Changing IsRed to TRUE for car with ID 123
UPDATE CARS SET IsRed = TRUE WHERE ID = 123;

In the above example, we updated the IsRed column for a specific car with the ID of 123, setting it to TRUE.

To update Boolean values in SQL Server, use the following query −

-- Changing IsRed to TRUE for car with ID 123
UPDATE CARS SET IsRed = 1 WHERE ID = 123;

The SQL LIKE Operator

The SQL LIKE operator is used to retrieve the data in a column of a table, based on a specified pattern.

It is used along with the WHERE clause of the UPDATE, DELETE and SELECT statements, to filter the rows based on the given pattern. These patterns are specified using Wildcards.

Suppose we need to submit the list of all the students whose name starts with ”K”. We can obtain this with the help of the LIKE operator as follows −

WHERE student_name LIKE ''K%

Here, the % is a wild card which represents zero, one or multiple characters. And the expression K% specifies that it will display the list of all the students whose name starts with ”k”.

The LIKE operator can be used with strings, numbers, or date values. However, using the string values is recommended.

Syntax

The basic syntax of the SQL LIKE operator is as follows −

SELECT column1, column2, ...
FROM table_name
WHERE columnn LIKE specified_pattern;

What are wild cards?

SQL wildcards are special characters used in SQL queries to match patterns in the data. Following are the wildcards used in conjunction with the LIKE operator in MySQL database −

In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other.

The table given below has a few examples showing the WHERE clause having different LIKE operators with ”%” and ”_” −

The ”%” Wildcard character

The % sign represents zero or multiple characters. The ”%” wildcard matches any length of a string which even includes the zero length.

Example

To understand it better let us consider 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 table will be created as follows −

Now, let us display all the records from the CUSTOMERS table, where the SALARY starts with 200 −

SELECT * FROM CUSTOMERS WHERE SALARY LIKE ''200%

Output

This would produce the following result −

Example

Below is the query that displays all the records from the CUSTOMERS table previously created with the NAME that has ”al” in any position. Here, we are using multiple ”%” wildcards in the LIKE condition −

SELECT * FROM CUSTOMERS WHERE NAME LIKE ''%al%

Output

The following result is produced −

The ”_” wildcard character

The underscore wild card represents a single number or character. A single ”_” looks for exactly one character similar to the ”%” wildcard.

Example

Following is the query which would display all the records from the CUSTOMERS table previously created, where the Name starts with K and is at least 4 characters in length −

SELECT * FROM CUSTOMERS WHERE NAME LIKE ''K___%

Output

The result obtained is given below −

Example

Following is the query to display all the records from the CUSTOMERS table, where the NAME has ”m” in the third position −

SELECT * FROM CUSTOMERS WHERE NAME LIKE ''__m%

Output

We get the following result on executing the above query −

LIKE operator with OR

We can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.

Syntax

Following is the basic syntax of using LIKE operator with OR operator −

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE pattern1 OR column2 LIKE pattern2 OR ...;

Example

Here, the SQL query retrieves the records of the customers whose name starts with C and ends with i, or customers whose name ends with k −

SELECT * FROM CUSTOMERS WHERE NAME LIKE ''C%i'' OR NAME LIKE ''%k

Output

This will produce the following result −

NOT operator with the LIKE condition

We use the NOT operator with LIKE to extract the rows which does not contain a particular string provided in the search pattern.

Syntax

Following is the basic syntax of NOT LIKE operator in SQL −

SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;

Example

In the query given below, we are fetching all the customers whose name does not start with K −

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE ''K%

Output

This will produce the following result −

Escape characters with LIKE operator

The escape character in SQL is used to exclude certain wildcard characters from the expression of the LIKE operator. By doing so, we can use these characters in their general sense.

Using the escape character, we can also avoid using the characters that are reserved in SQL syntax to denote specific commands, such as the single quote ”, % and _.

For example, if you need to search for % as a literal in the LIKE condition, then it is done using Escape character.

An escape character is only defined as a single character. It is suggested to choose the character which is not present in our data.

Syntax

The syntax for using the LIKE operator with escape characters is as follows −

SELECT column1, column2, ...
FROM table_name
WHERE column1 LIKE ''pattern ESCAPE escape_character

Where,

• pattern is the pattern you want to match.

• ESCAPE is the keyword that indicates the escape character

• escape_character is the character that you want to use as the escape character.

Example

Let us create a new table EMPLOYEE using the query below −

CREATE TABLE EMPLOYEE (
   SALARY DECIMAL (18,2) NOT NULL,
   BONUS_PERCENT VARCHAR (20)
);

Now, we can insert values into this empty tables using the INSERT statement as follows −

INSERT INTO EMPLOYEE VALUES
(67000.00, ''45.00''),
(54000.00, ''20.34%''),
(75000.00, ''51.00''),
(84000.00, ''56.82%'');

The Employee table consists of the salary of employees in an organization and the bonus percentage in their salary as shown below −

Now, we are displaying all the records from the EMPLOYEE table, where the BONUS_PERCENT contains the % literal −

SELECT * FROM EMPLOYEE
WHERE BONUS_PERCENT LIKE''%!%%'' ESCAPE ''!

Output

This will produce the following result −

Example

In here, we are retrieving the BONUS_PERCENT that starts with 2 and contains the % literal −

SELECT * FROM EMPLOYEE
WHERE BONUS_PERCENT LIKE''2%!%%'' ESCAPE ''!

Output

Following result is obtained −

Uses of LIKE Operator in SQL

The few uses of LIKE operators are given below −

• It helps us to extract data that matches with the required pattern.

• It helps us in performing complex regex-based queries on our data.

• It simplifies the complex queries.

The SQL IN Operator

The SQL IN Operator is used to specify multiple values or sub query in the WHERE clause. It returns all rows in which the specified column matches one of the values in the list. The list of values or sub query must be specified in the parenthesis e.g. IN (select query) or IN (Value1, Value2, Value3, …).

In some scenarios we may use multiple OR statements to include multiple conditions in SELECT, DELETE, UPDATE, or INSERT statements. Alternatively, we can use the IN operator instead of multiples OR statements.

The IN operator can be used with any data type in SQL. It is used to filter data from a database table based on specified values.

The IN operator is useful when you want to select all rows that match one of a specific set of values. While the OR operator is useful when you want to select all rows that match any one of multiple conditions.

Syntax

The basic syntax of the SQL IN operator to specify multiple values is as follows −

WHERE column_name IN (value1, value2, value3, ...);

Where,

• value1, value2, value3, … are the values in the list to be tested against the expression. The IN operator returns TRUE if any of these values is found in the list, and FALSE if it is not.

IN Operator with SELECT Statement

We can use the SQL IN operator to specify multiple values in a WHERE clause, and we can also use it in a SELECT statement to retrieve data that matches any of the specified values.

Here, we are using the IN operator to specify multiple values in SELECT statement.

Example

In this example, we are using the IN operator to specify multiple values in SELECT statement consider 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 table will be created as follows −

Suppose based on the above table we want to display records with NAME equal to ”Khilan”, ”Hardik” and ”Muffy”(string values). This can be achieved using IN operator as follows −

SELECT * FROM CUSTOMERS
WHERE NAME IN (''Khilan'', ''Hardik'', ''Muffy'');

Output

The result obtained is as follows −

Example

The above query can also be done using OR operator. Following is an example −

SELECT * FROM CUSTOMERS
WHERE NAME = ''Khilan'' OR NAME = ''Hardik'' OR NAME = ''Muffy

Output

IN Operator with UPDATE Statement

We can also use the SQL IN operator in an UPDATE statement to update rows that match any of the specified values in a WHERE clause. The UPDATE statement is used to modify existing data in a database table.

Example

Here, we are using the IN operator to specify multiple values in the UPDATE statement and updating the CUSTOMERS table previously created. Here, we are changing the records of the customers with age ”25” or ”27” and updating the age value to ”30” −

UPDATE CUSTOMERS SET AGE = 30 WHERE AGE IN (25, 27);

Output

We get the following result. We can observe that the age of 3 customers has been modified −

Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

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 CUSTOMERS table −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the AGE of ”Khilan”, ”Chaitali” and ”Hardik” has been updated to ”30”.

IN Operator with NOT

To negate a condition, we use the NOT operator. The SQL IN operator can be used in combination with the NOT operator to exclude specific values in a WHERE clause. In other words, the absence of a list from an expression will be checked.

Syntax

Following is the basic syntax of NOT IN operator −

WHERE column_name NOT IN (value1, value2, ...);

Example

Now, we are displaying all the records from the CUSTOMERS table, where the AGE is NOT equal to ”25”, ”23” and ”22” −

SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 23, 22);

Output

We obtain the result as given below −

IN Operator with Column Name

We can also use the SQL IN operator with a column name to compare the values of one column to another. It is used to select the rows in which a specific value exists for the given column.

Example

In the below query, we are selecting the rows with the value ”2000” in the SALARY column −

SELECT * FROM CUSTOMERS WHERE 2000 IN (SALARY);

Output

This would produce the following result −

Subquery with IN Operator

We can use the subquery with the IN operator that is used to return records from the single column. This means that more than one column in the SELECT column list cannot be included in the subquery.

Syntax

The basic syntax of the IN operator to specify a subquery is as follows −

WHERE column_name IN (subquery);

Where,

• Subquery − This is the SELECT statement that has a result set to be tested against the expression. The IN condition evaluates to true if any of these values match the expression.

Example

In the query given below, we are displaying all the records from the CUSTOMERS table where the NAME of the customer is obtained with SALARY greater than 2000 −

SELECT * FROM CUSTOMERS
WHERE NAME IN (SELECT NAME FROM CUSTOMERS WHERE SALARY > 2000);

Output

This will produce the following result −

The SQL ANY and ALL operators are used to perform a comparison between a single value and a range of values returned by the subquery.

The ANY and ALL operators must be preceded by a standard comparison operator i.e. >, >=, <, <=, =, <>, != and followed by a subquery. The main difference between ANY and ALL is that ANY returns true if any of the subquery values meet the condition whereas ALL returns true if all of the subquery values meet the condition.

The SQL ANY Operator

The ANY operator is used to verify if any single record of a query satisfies the required condition.

This operator returns a TRUE, if the given condition is satisfied for any of the values in the range. If none of the values in the specified range satisfy the given condition, this operator returns false. You can also use another query (subquery) along with this operator.

Syntax

The basic syntax of the SQL – ANY operator is as follows −

Column_name operator ANY (subquery);

Where,

• column_name is the name of a column in the main query.

• operator is a comparison operator such as =, <, >, <=, >=, or <>.

• subquery is a SELECT statement that returns a single column of values.

ANY with ”>” Operator

Typically, the ANY operator is used to compare a value with a set of values returned by a subquery, in such cases we can use it with the > (greater than) operator to verify if a particular column value is greater than column value of any of the records returned by the sub query.

Example

To understand it better let us consider 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 table will be created as follows −

Now, let us list out the details of all the CUSTOMERS whose SALARY is greater than the SALARY of any customer whose AGE is 32 i.e. Chaitali, Hardik, Komal and Muffy in this case −

SELECT * FROM CUSTOMERS
WHERE SALARY > ANY (SELECT SALARY FROM CUSTOMERS WHERE AGE = 32);

Output

The result obtained is as follows −

ANY with ”<” Operator

Similar to the ”>” operator, we can use the ”<” (less than) operator along with ANY to verify if a particular column value is less than column value of any of the records returned by the sub query.

Example

In here, we are finding the distinct/different age of customers having any salary less than the average salary of all the customers from the CUSTOMERS table previously created −

SELECT DISTINCT AGE FROM CUSTOMERS
WHERE SALARY < ANY (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

We get the following output while executing the above query −

ANY with ”=” Operator

When we use the = (equal to) operator along with ANY, it verifies if a particular column value is equal to the column value of any of the records returned by the sub query.

Example

In the query given below, we are retrieving the details of all the customers whose age is equal to the age of any customer whose name starts with ”K” −

SELECT * FROM CUSTOMERS
WHERE AGE = ANY (SELECT AGE FROM CUSTOMERS WHERE NAME LIKE ''K%'');

Output

The result produced is as follows −

The SQL ALL Operator

The SQL ALL operator returns all the records of the SELECT statement.

• It returns TRUE if the given condition is satisfied for ALL the values in the range.

• It always returns a Boolean value.

• It is used with SELECT, WHERE and HAVING statements in SQL queries.

• The data type of the values returned from a subquery must be the same as the outer query expression data type.

Syntax

The basic syntax of the SQL ALL operator is as follows −

Column_name operator ALL (subquery);

Where,

• column_name − is the name of a column in the main query.

• operator − is a comparison operator such as =, <, >, <=, >=, or <>.

• subquery − is a SELECT statement that returns a single column of values.

ALL with WHERE Statement

When we use the ALL operator with a WHERE clause, it filters the results of the subquery based on the specified condition.

The WHERE clause in SQL is used to filter rows from a query based on specific conditions. It operates on individual rows in the table, and it allows you to specify conditions that must be met by each row in the data returned by the query.

Example

If we consider the CUSTOMERS table created above,the following query returns the details of all the customers whose salary is not equal to the salary of any customer whose age is 25 −

SELECT * FROM CUSTOMERS
WHERE SALARY <>
ALL (SELECT SALARY FROM CUSTOMERS WHERE AGE = 25);

Output

The output of the above query is as follows −

ALL with HAVING Clause

In SQL, the ALL operator can also be used with the HAVING clause to filter the results of a GROUP BY query based on a condition that applies to all the aggregated values in the group.

Example

The following SQL query is used to obtain the details of all the customers whose salary is less than the average salary −

SELECT NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS
GROUP BY AGE, SALARY
HAVING SALARY < ALL (SELECT AVG(SALARY) FROM CUSTOMERS);

Output

Output of the above query is as follows −