A NULL value indicates a missing or unknown value. It appears to be blank and does not contain any data. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. For checking null values we can use two basic operators.

• IS NULL
• IS NOT NULL

The SQL IS NOT NULL Operator

The SQL IS NOT NULL operator is used to filter data by verifying whether a particular column has a not-null values. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.

By using the IS NOT NULL operator, we can only fetch the records that contain valid data in a particular column.

Syntax

Following is the syntax of the SQL IS NOT NULL operator −

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Example

Firstly, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(20),
   AGE INT,
   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'', NULL ),
(2, ''Khilan'', 25, NULL, 1500.00 ),
(3, ''Kaushik'', NULL, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', NULL ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', NULL, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, NULL, 10000.00 );

The table will be created as follows −

Example

In the following query, we are going to return all the records from the CUSTOMERS table where the ADDRESS is not null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL;

Output

On executing the above query, it will generate the output as shown below −

IS NOT NULL with COUNT() Function

We can use the IS NOT NULL operator along with the SQL COUNT() function to count only the non-null values in a specific column.

Syntax

Following is the syntax of IS NOT NULL operator with the COUNT() function −

SELECT COUNT(column_name)
FROM table_name
WHERE condition IS NOT NULL;

Example

The following query returns the count of all rows in the CUSTOMERS table where the SALARY column is not null −

SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NOT NULL;

Output

The output produced is as shown below −

IS NOT NULL with DELETE Statement

In SQL, we can delete all rows that do not contain NULL values in a specific column using the DELETE statement with IS NOT NULL operator.

Syntax

Following is the syntax of the IS NOT NULL operator with the DELETE statement in SQL −

DELETE FROM table_name
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

In the following query, we are deleting records which are not null in the SALARY column of the CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL;

Output

We get the following result −

Query OK, 5 rows affected (0.02 sec)

Verification

Execute the SELECT query given below to check whether the table has been changed or not −

SELECT * FROM CUSTOMERS;

If we compile and run the program, the result is produced as follows −

IS NOT NULL with UPDATE Statement

We can use the UPDATE statement with the IS NOT NULL operator in SQL to update records with not-null records in a particular column.

Syntax

Following is the syntax of the IS NOT NULL operator with the UPDATE statement in SQL −

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE columnname1, columnname2, ... IS NOT NULL;

Example

Truncate the CUSTOMERS table and reinsert all the 7 records into it again. The following query, increments all the values in the SALARY column of the with 5000, where the salary value is not null −

UPDATE CUSTOMERS SET SALARY = SALARY+5000 WHERE SALARY IS NOT NULL;

Output

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

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

Verification

To check whether the table has been updated or not, execute the SELECT query below −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

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 GROUP BY Clause

The SQL GROUP BY clause is used in conjunction with the SELECT statement to arrange identical data into groups. This clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY and HAVING clauses (if they exist).

The main purpose of grouping the records of a table based on particular columns is to perform calculations on these groups. Therefore, The GROUP BY clause is typically used with aggregate functions such as SUM(), COUNT(), AVG(), MAX(), or MIN() etc.

For example, if you have a table named SALES_DATA containing the sales data with the columns YEAR, PRODUCT, and SALES. To calculate the total sales in an year, the GROUP BY clause can be used to group the records in this table based on the year and calculate the sum of sales in each group using the SUM() function.

Syntax

Following is the basic syntax of the SQL GROUP BY clause −

SELECT column_name(s)
FROM table_name
GROUP BY column_name(s);

Where, column_name(s) refers to the name of one or more columns in the table that we want to group the data by and the table_name refers to the name of the table that we want to retrieve data from.

GROUP BY Clause with Aggregate Functions

Typically, we group the record of a table to perform calculations on them. Therefore, the SQL GROUP BY clause is often used with the aggregate functions such as SUM(), AVG(), MIN(), MAX(), COUNT(), etc.

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, 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 created is as shown below −

The following SQL query groups the CUSTOMERS table based on AGE and counts the number of records in each group −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

Output

Following is the result produced −

Example

In the following query, we are finding the highest salary for each age −

SELECT AGE, MAX(salary) AS MAX_SALARY
FROM CUSTOMERS GROUP BY AGE;

Output

Following is the output of the above query −

Similarly we can group the records of the CUSTOMERS table based on the AGE column and calculate the maximum salary, average and sum of the SALARY values in each group using the MIN(), AVG() and SUM() functions respectively.

GROUP BY Clause on Single Columns

When we use the GROUP BY clause with a single column, all the rows in the table that have the same value in that particular column will be merged into a single record.

Example

In the following example we are grouping the above created CUSTOMERS table by the ADDRESS column and calculating the average salary of the customer from each city −

SELECT ADDRESS, AVG(SALARY) as AVG_SALARY
FROM CUSTOMERS GROUP BY ADDRESS;

Output

This would produce the following result −

GROUP BY Clause with Multiple Columns

When we use the GROUP BY clause with multiple columns, all the rows in the table that have the same values in all of the specified columns will be merged into a single group.

Example

In the following query we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and −

SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY
FROM CUSTOMERS GROUP BY ADDRESS, AGE;

Output

This would produce the following result −

GROUP BY with ORDER BY Clause

We can use the ORDER BY clause with GROUP BY in SQL to sort the grouped data by one or more columns.

Syntax

Following is the syntax for using ORDER BY clause with GROUP BY clause in SQL −

SELECT column1, column2, ..., aggregate_function(columnX) AS alias
FROM table
GROUP BY column1, column2, ...
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Example

In here, we are finding the highest salary for each age, sorted by high to low −

SELECT AGE, MIN(SALARY) AS MIN_SALARY
FROM CUSTOMERS
GROUP BY AGE ORDER BY MIN_SALARY DESC;

Output

Following is the result produced −

GROUP BY with HAVING Clause

We can also use the GROUP BY clause with the HAVING clause filter the grouped data in a table based on specific criteria.

Syntax

Following is the syntax for using ORDER BY clause with HAVING clause in SQL −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

Example

In the following query, we are grouping the customers by their age and calculating the minimum salary for each group. Using the HAVING clause we are filtering the groups where the age is greater than 24 −

SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM
FROM CUSTOMERS
GROUP BY ADDRESS, AGE HAVING AGE>24;

Output

The result produced is as follows −

The SQL BETWEEN Operator

The BETWEEN operator is a logical operator in SQL, that is used to retrieve the data within a specified range. The retrieved values can be integers, characters, or dates.

You can use the BETWEEN operator to replace a combination of “greater than equal AND less than equal” conditions.

Let us understand it in a better way by using the below example table −

Suppose we want to list out the names from the above table who are aged BETWEEN 20 and 30. So, we will get “Varma(21)”, “Nikhil(25)”, and “Bhargav(29)” as a result.

Syntax

Following is the syntax of the BETWEEN operator in SQL −

SELECT column1, column2, column3,....columnN
FROM table_name
WHERE column BETWEEN value1 AND value2;

Here,

• value1 is the beginning value of the range.
• value2 is the ending value of the range (inclusive).

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 (18, 2),
   PRIMARY KEY (ID)
);

Once the table is created, let us insert some values into the table using the following INSERT query −

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 created is as follows −

Now, we are using the BETWEEN operator to retrieve the details of the CUSTOMERS whose AGE (numeric data) is between 20 and 25 −

SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 25;

Output

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

Example

Here, we are using the BETWEEN operator with characters. Let us retrieve the details of the customers whose names starts in between the alphabets “A” and “L” using the following query −

SELECT * FROM CUSTOMERS WHERE NAME BETWEEN ''A'' AND ''L

Output

Following is the output of the above query −

BETWEEN Operator with IN Operator

In SQL, we can combine the BETWEEN operator with the IN operator to select values that are within a specified range and also matches with values specified in the list of IN clause.

Example

In the following query, we are retrieving the details of all the customers whose salary is between 4000 and 10000. In addition, we are only retrieving the customers who lives in Hyderabad and Bhopal using the IN operator in SQL −

SELECT * FROM CUSTOMERS
WHERE SALARY BETWEEN 4000 AND 10000
  AND ADDRESS IN (''Hyderabad'', ''Bhopal'');

Output

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

BETWEEN Operator with UPDATE Statement

We can also use the BETWEEN operator with the UPDATE statement to update values within the specified range. The UPDATE statement is used to modify existing data in a database table.

Example

Let us update the salaries of the customers whose age lies between 25 to 30 using the following query −

UPDATE CUSTOMERS SET SALARY = 10000
WHERE AGE BETWEEN 25 AND 30;

Output

The output for the above query is as given below −

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

Verification

Let us verify whether the salaries are updated or not using the following query −

SELECT * FROM CUSTOMERS;

The table for the above query produced as given below −

BETWEEN Operator with DELETE Statement

We can also use the BETWEEN operator with the DELETE statement to delete rows within a specified range.

Example

Now, let us delete the customers whose age is between 20 and 24 using the DELETE statement −

DELETE FROM CUSTOMERS
WHERE AGE BETWEEN 20 AND 24;

Output

If we compile and run the above query, the result is produced as follows −

Query OK, 3 rows affected (0.02 sec)

Verification

Let us verify whether the records with the specified age values are deleted or not, using the following query −

SELECT * FROM CUSTOMERS;

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

NOT BETWEEN Operator

The NOT BETWEEN operator in SQL works exactly opposite to BETWEEN operator. This is used to retrieve the data which is not present in the specified range.

Let us understand in a better way by using the below example table −

Suppose we want to list out the students from the above table who are aged not between 20 and 30. So, we will get “Prudhvi(45) and Ganesh(33)” as result.

Syntax

Following is the syntax of the NOT BETWEEN operator in SQL −

SELECT column_name1, column_name2, column_name3,......column_nameN
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

Consider the previously created CUSTOMERS table and let us retrieve the details of customers whose age is not greater than or equal to 25 and less than or equal to 30 (numeric data) using the following query −

SELECT * FROM CUSTOMERS
WHERE AGE NOT BETWEEN 25 AND 30;

Output

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

NOT BETWEEN Operator with IN

We can use the NOT BETWEEN operator in combination with the IN operator to select values that are outside a range and also do not match with the specified list of values.

Example

In the following query, we are selecting the customers whose salary is not between 1000 and 5000. In addition; we are not retrieving the customers who are living in Bhopal using the IN operator in SQL −

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1000 AND 5000
  AND ADDRESS NOT IN (''Bhopal'');

Output

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

The SQL DISTINCT Keyword

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to fetch unique records from a table.

We use DISTINCT keyword with the SELECT statetment when there is a need to avoid duplicate values present in any specific columns/tables. When we use DISTINCT keyword, SELECT statement returns only the unique records available in the table.

The SQL DISTINCT Keyword can be associated with SELECT statement to fetch unique records from single or multiple columns/tables.

Syntax

The basic syntax of SQL DISTINCT keyword is as follows −

SELECT DISTINCT column1, column2,.....columnN
FROM table_name;

Where, column1, column2, etc. are the columns we want to retrieve the unique or distinct values from; and table_name represents the name of the table containing the data.

DISTINCT Keyword on Single Columns

We can use the DISTINCT keyword on a single column to retrieve all unique values in that column, i.e. with duplicates removed. This is often used to get a summary of the distinct values in a particular column or to eliminate redundant data.

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

First, let us retrieve the SALARY values from the CUSTOMERS table using the SELECT query −

SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;

This would produce the following result. Here, you can observe that the salary value 2000 is appearing twice −

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

This would produce the following result where we do not have any duplicate entry −

DISTINCT Keyword on Multiple Columns

We can also use the DISTINCT keyword on multiple columns to retrieve all unique combinations of values across those columns. This is often used to get a summary of distinct values in multiple columns, or to eliminate redundant data.

Example

In the following query, we are retrieving a list of all unique combinations of customer”s age and salary using the DISTINCT keyword −

SELECT DISTINCT AGE, SALARY FROM CUSTOMERS ORDER BY AGE;

Output

Though the AGE column have the value “25” in two records, each combination of “25” with it”s specific ”salary” is unique, so both rows are included in the result set −

DISTINCT Keyword with COUNT() Function

The COUNT() function is used to get the number of records retuned by the SELECT query. We need to pass an expression to this function so that the SELECT query returns the number of records that satisfy the specified expression.

If we pass the DISTINCT keyword to the COUNT() function as an expression, it returns the number of unique values in a column of a table.

Syntax

Following is the syntax for using the DISTINCT keyword with COUNT() function −

SELECT COUNT(DISTINCT column_name)
FROM table_name WHERE condition;

Where, column_name is the name of the column for which we want to count the unique values; and table_name is the name of the table that contains the data.

Example

In the following query, we are retrieving the count of distinct age of the customers −

SELECT COUNT(DISTINCT AGE) as UniqueAge  FROM CUSTOMERS;

Output

Following is the result produced −

DISTINCT Keyword with NULL Values

In SQL, when there are NULL values in the column, DISTINCT treats them as unique values and includes them in the result set.

Example

First of all let us update two records of the CUSTOMERS table and modify their salary values to NULL

UPDATE CUSTOMERS SET SALARY = NULL WHERE ID IN(6,4);

The resultant CUSTOMERS table would be −

Now, we are retrieving the distinct salary of the customers using the following query −

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

Output

Following is the output of the above query −

The SQL HAVING Clause

The SQL HAVING clause is similar to the WHERE clause; both are used to filter rows in a table based on specified criteria. However, the HAVING clause is used to filter grouped rows instead of single rows. These rows are grouped together by the GROUP BY clause, so, the HAVING clause must always be followed by the GROUP BY clause.

Moreover, the HAVING clause can be used with aggregate functions such as COUNT(), SUM(), AVG(), etc., whereas the WHERE clause cannot be used with them.

Syntax

Following is the basic syntax of the SQL HAVING clause −

SELECT column1, column2, aggregate_function(column)
FROM table_name
GROUP BY column1, column2
HAVING condition;

The following code block shows the position of the HAVING Clause in a query −

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

HAVING with GROUP BY Clause

We can use the HAVING clause with the GROUP BY clause to filter groups of rows that meet certain conditions. It is used to apply a filter to the result set after the aggregation has been performed.

Example

Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary, 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 created is as shown below −

Now, we are grouping the records of the CUSTOMERS table based on the columns ADDRESS and AGE and filtering the groups where the AGE value is less than 25.

SELECT ADDRESS, AGE, MIN(SALARY) AS MIN_SUM
FROM CUSTOMERS
GROUP BY ADDRESS, AGE HAVING AGE > 25;

Output

The result produced is as follows −

HAVING with ORDER BY Clause

The ORDER BY clause is used to arrange/sort the records of the result of a SELECT query based on a specific column (either in ascending order or in descending order). If we use the ORDER BY clause with the HAVING clause we can sort the filtered groups in the desired order.

Example

Following query groups the records of the CUSTOMERS table based on the columns AGE and ADDRESS, filters the groups where the SALARY value is less than 5000 and, arranges the remaining groups in descending order based the total salaries of each group.

SELECT ADDRESS, AGE, SUM(SALARY) AS TOTAL_SALARY
FROM CUSTOMERS
GROUP BY ADDRESS, AGE HAVING TOTAL_SALARY >=5000
ORDER BY TOTAL_SALARY DESC;

Output

The result produced is as follows −

HAVING Clause with COUNT() Function

The HAVING clause can be used with the COUNT() function to filter groups based on the number of rows they contain.

Example

Following query groups the records of the CUSTOMERS table based on the AGE column and, retrieves the details of the group that has more than two entities −

SELECT AGE, COUNT(AGE)
FROM CUSTOMERS GROUP BY AGE HAVING COUNT(age) > 2;

Output

This would produce the following result −

Query OK, 0 rows affected (0.02 sec)

HAVING Clause with AVG() Function

The HAVING clause can also be used with the AVG() function to filter groups based on the average value of a specified column.

Example

Now, we are retrieving the city of the customers whose average salary is greater than 5240 −

SELECT ADDRESS, AVG(SALARY) as AVG_SALARY
FROM CUSTOMERS
GROUP BY ADDRESS HAVING AVG(SALARY) > 5240;

Output

Following is the output of the above query −

HAVING Clause with MAX() Function

We can also use the HAVING clause with MAX() function to filter groups based on the maximum value of a specified column.

Example

Now, we are retrieving the city of the customers whose maximum salary is greater than 5240 −

SELECT ADDRESS, MAX(SALARY) as MAX_SALARY
FROM CUSTOMERS
GROUP BY ADDRESS HAVING MAX(SALARY) > 5240;

Output

The result obtained is as follows −

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.