The SQL Join Clause

The SQL Join clause is used to combine data from two or more tables in a database. When the related data is stored across multiple tables, joins help you to retrieve records combining the fields from these tables using their foreign keys.

The part of the Join clause that specifies the columns on which records from two or more tables are joined is known as join-predicate. This predicate is usually specified along with the ON clause and uses various comparison operators such as, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT etc. We can also connect multiple join predicates with logical operators AND, OR, and NOT.

We can use JOINs along with and , SQL queries to update and delete records from across multiple tables. When you retrieve a table using joins, the resultant table displayed is not stored anywhere in the database.

Syntax

Following is the basic syntax of a the SQL JOIN CLAUSE −

SELECT column_name(s)
FROM table1
JOIN table2;

Example

Assume we have created a CUSTOMERS table that contains details of the customers of an organization 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 CUSTOMERS table will be created as follows −

Following is another table ORDERS which contains the order details made by the customers.

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

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

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

The ORDERS table will be created as follows −

Following query performs the join operation on the tables CUSTMERS and ORDERS −

SELECT ID, NAME, AGE, AMOUNT
FROM CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

By executing the query above, the resultant table is displayed and contains the values present in ID, NAME, AGE fields of CUSTOMERS table and AMOUNT field of ORDERS table.

Types of joins in SQL

SQL provides various types of Joins that are categorized based on the way data across multiple tables are joined together. They are listed as follows −

Inner Join

An is the default join which retrieves the intersection of two tables. It compares each row of the first table with each row of the second table. If the pairs of these rows satisfy the join-predicate, they are joined together.

Outer Join

An Outer Join retrieves all the records in two tables even if there is no counterpart row of one table in another table, unlike Inner Join. Outer join is further divided into three subtypes – Left Join, Right Join and Full Join.

Following are the different types of outer Joins −

• − returns all rows from the left table, even if there are no matches in the right table.

• − returns all rows from the right table, even if there are no matches in the left table.

• − returns rows when there is a match in one of the tables.

Other Joins

In addition to these there are two more joins −

• − is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

• − returns the Cartesian product of the sets of records from the two or more joined tables.

An SQL Join clause is used to combine multiple related tables in a database, based on common fields/columns.

There are two major types of joins: Inner Join and Outer Join. Other joins like Left Join, Right Join, Full Join etc. Are just subtypes of these two major joins. In this tutorial, we will only learn about the Inner Join.

The SQL Inner Join

The SQL Inner Join is a type of join that combines multiple tables by retrieving records that have matching values in both tables (in the common column).

It compares each row of the first table with each row of the second table, to find all pairs of rows that satisfy the join-predicate. When the join-predicate is satisfied, the column values from both tables are combined into a new table.

The Inner Join is also referred as Equijoin. It is the default join; i.e., even if the “Join“keyword is used instead of “Inner Join“, tables are joined using matching records of common columns.

Explanation

Let us look at an example scenario to have a better understanding.

Suppose we have the information of employees in a company divided between two tables namely EmpDetails and Marital status. Where,

• EmpDetails table holds details like Employee ID, Name and Salary.

• MaritalStatus table holds the details Employee ID, Age, and Marital Status.

When we perform the Inner Join operation on these two tables based on the join-predicate EmpDetails.EmpID = MaritalStatus.EmpID, the resultant records hold the following info: ID, Name, Salary, Age and, Status of the matched records.

Syntax

Following is the basic syntax of SQL Inner Join −

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

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 −

Let us now combine these two tables using the Inner Join query as shown below −

SELECT ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output

The result of this query is obtained as follows −

Joining Multiple Tables Using Inner Join

Until now, we have only learnt how to join two tables using Inner Join. However, we can also join as many tables as possible, using Inner Join, by specifying the condition (with which these tables are to be joined).

Syntax

Following is the syntax to join more than two tables using Inner Join −

SELECT column1, column2, column3...
FROM table1
INNER JOIN table2
ON condition_1
INNER JOIN table3
ON condition_2
....
....
INNER JOIN tableN
ON condition_N;

Note that, even in this case, only two tables can be joined together on a single condition. This process is done sequentially until all the tables are combined.

Example

Let us make use of the previous tables CUSTOMERS and ORDERS along with a new table EMPLOYEE. We will create the EMPLOYEE table using the query below −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

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

INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

The details of EMPLOYEE table can be seen below.

Using the following query, we can combine three tables CUSTOMERS, ORDERS and EMPLOYEE.

SELECT OID, DATE, AMOUNT, EMPLOYEE_NAME FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
INNER JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Output

The result of the inner join query above is shown as follows −

Inner Join with WHERE Clause

Clauses in SQL work with the purpose of applying constraints while retrieving data using SQL queries. There are various clauses that SQL uses to constraint the data; such as WHERE clause, GROUP BY clause, ORDER BY clause, UNION clause etc.

The WHERE clause is used to filter the data from tables. This clause specifies a condition to retrieve only those records that satisfy it.

Inner Join uses WHERE clause to apply more constraints on the data to be retrieved. For instance, while retrieving the employee records of an organization, if we only want to check the data of employees that earn more than 25000 in a month, we need to specify a WHERE condition (salary > 25000) to retrieve only those employee records.

Syntax

The syntax of Inner Join when used with WHERE clause is given below −

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

Example

In this example we are joining the tables CUSTOMERS and ORDERS using the inner join query and we are applying some constraints on the result using the WHERE clause.

Here, we are retrieving the ID and NAME from the CUSTOMERS table and DATE and AMOUNT from the ORDERS table where the amount paid is higher than 2000.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
INNER JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 2000.00;

Output

The resultant table after applying the where clause with inner join contains the rows that has AMOUNT values greater than 2000.00 −

You can rename a table or a column in a database temporarily by giving them another pseudo name. This pseudo name is known as Alias. The use of aliases is to address a specific table or a column in an SQL statement without changing their original name in the database. Aliases are created with the AS keyword.

Aliases can be especially useful when working with complex queries involving multiple tables or columns with similar names. By assigning temporary names to these tables or columns, you can make your SQL query more readable and easier to understand.

The SQL Aliasing

Aliases are used to address database tables with a shorter or more meaningful name within an SQL query. The basic syntax of a table alias is as follows.

SELECT column1, column2....
FROM table_name AS alias_name;

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

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

CREATE TABLE ORDERS (
   OID INT NOT NULL,
   DATES 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 obtained is as shown below −

Now, the following query shows the usage of a table alias. The CUSTOMERS table is aliased as ”C” and the ORDERS table is aliased as ”O” −

SELECT C.ID, C.NAME, C.AGE, O.AMOUNT
FROM CUSTOMERS AS C, ORDERS AS O
WHERE  C.ID = O.CUSTOMER_ID;

Output

This would produce the following result −

Aliasing Column Names

We can also use an alias for a column name in SQL to give it a different name in the result set of a query. The basic syntax of a column alias is as follows −

SELECT column_name AS alias_name
FROM table_name;

Example

Following is the usage of a column alias. Here, the NAME column is aliased as ”CUSTOMER_NAME” −

SELECT ID AS CUSTOMER_ID, NAME AS CUSTOMER_NAME
FROM CUSTOMERS;

Output

This would produce the following result −

Aliasing with Self Join

The SQL Self Join is used to join a table to itself as if the table were two tables. During this process, we need to use alias for one of the tables with a temporary name to avoid misunderstandings. This renaming is done using aliases.

Syntax

Following is the syntax for performing a self-join with aliases −

SELECT column_name(s)
FROM my_table a, my_table b
ON a.join_column = b.join_column;

Example

Now, let us join the CUSTOMERS table to itself using the following Self Join query. Our aim is to establish a relationship among customers on the basis of their earnings. In here, we are using aliases with column names as well as with the table names −

SELECT
   a.ID, b.NAME as EARNS_HIGHER,
   a.NAME as EARNS_LESS,
   a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY
Output
Output of the above query is as follows −


ID
EARNS_HIGHER
EARNS_LESS
LOWER_SALARY


2
Ramesh
Khilan
1500.00


2
Kaushik
Khilan
1500.00


6
Chaitali
Komal
4500.00


3
Chaitali
Kaushik
2000.00


2
Chaitali
Khilan
1500.00


1
Chaitali
Ramesh
2000.00


6
Hardik
Komal
4500.00


4
Hardik
Chaitali
6500.00


3
Hardik
Kaushik
2000.00


2
Hardik
Khilan
1500.00


1
Hardik
Ramesh
2000.00


3
Komal
Kaushik
2000.00


2
Komal
Khilan
1500.00


1
Komal
Ramesh
2000.00


6
Muffy
Komal
4500.00


5
Muffy
Hardik
8500.00


4
Muffy
Chaitali
6500.00


3
Muffy
Kaushik
2000.00


2
Muffy
Khilan
1500.00


1
Muffy
Ramesh
2000.00











		

	

SQL Joins are used to retrieve records from multiple tables based on a given condition. A Join includes the records that satisfy the given condition and outer join results a table that contains both matched and unmatched rows.

Left Outer Join, as discussed in the previous tutorial, is used to find the union of two tables with respect to the left table. In this tutorial, let us discuss about the Right outer join.

The SQL Right Join

The Right Join or Right Outer Join query in SQL returns all rows from the right table, even if there are no matches in the left table. In short, a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

If the ON clause matches zero records in the left table; the join will still return a row in the result, but with a NULL value in each column of the left table.

Syntax

Following is the basic syntax of Right Join in SQL −

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

Example

The tables we are using in this example are named CUSTOMERS and ORDERS.

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

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

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

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

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 −

Now, let us join these two tables using the Right Join query as follows −

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

Output

This would produce the following result −

Joining Multiple Tables with Right Join

Like Left Join, Right Join also joins multiple tables. However, the contrast occurs where the second table is returned as a whole instead of the first.

In addition, the rows of first table are matched with the rows in second table. If the records are not matched and the number of records in the second table is greater than the first, NULL is returned as the values in first table.

Syntax

Following is the syntax to join multiple tables using Right Join −

SELECT column1, column2, column3...
FROM table1
RIGHT JOIN table2
ON condition_1
RIGHT JOIN table3
ON condition_2
....
....
RIGHT JOIN tableN
ON condition_N;

Example

Here, let us consider the previously created tables CUSTOMERS and ORDERS; and create a new table named EMPLOYEE using the following query −

CREATE TABLE EMPLOYEE (
   EID INT NOT NULL,
   EMPLOYEE_NAME VARCHAR (30) NOT NULL,
   SALES_MADE DECIMAL (20)
);

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

INSERT INTO EMPLOYEE VALUES
(102, ''SARIKA'', 4500),
(100, ''ALEKHYA'', 3623),
(101, ''REVATHI'', 1291),
(103, ''VIVEK'', 3426);

The details of EMPLOYEE table can be seen below −

Following query joins these three tables using the Right Join query −

SELECT CUSTOMERS.ID, CUSTOMERS.NAME,
ORDERS.DATE, EMPLOYEE.EMPLOYEE_NAME
FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
RIGHT JOIN EMPLOYEE
ON ORDERS.OID = EMPLOYEE.EID;

Through this query, we will display the id, name of the customer along with the date on which the orders are made and the name of the employee who sold the item.

Output

The resultant table is obtained as follows −

Right Join with WHERE Clause

A WHERE Clause is used to filter out records that satisfy the condition specified by it. This clause can be used with the Right Join query to apply certain filters on the joined result-set.

Syntax

The syntax of Right Join when used with WHERE clause is given below −

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

Example

Records in the combined database tables can be filtered using the WHERE clause. Consider the previous two tables CUSTOMERS and ORDERS; and join them using the right join query by applying some constraints using the WHERE clause.

SELECT ID, NAME, DATE, AMOUNT FROM CUSTOMERS
RIGHT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE ORDERS.AMOUNT > 1000.00;

Output

The resultant table after applying the where clause with right join contains the rows that has amount values greater than 1000.00 −

The SQL CASE Statement

The SQL CASE statement is a conditional statement that helps us to make decisions based on a set of conditions. It evaluates the set of conditions and returns the respective values when a condition is satisfied.

The CASE statement works like a simplified IF-THEN-ELSE statement and allows for multiple conditions to be tested.

This starts with the keyword CASE followed by multiple conditionals statements. Each conditional statement consists of at least one pair of WHEN and THEN statements. Where WHEN specifies conditional statements and THEN specifies the actions to be taken.

It is often used to create a new column with values based on the value of an existing column.

Let us look at a simple scenario to understand this statement.

For e.g. when the credit limit of a customer is above ”10,000”, then the customer will be recognized as a ”High value customer when the credit limit is above ”5000”, then the customer will be recognized as a ”Mid value customer otherwise the customer will be recognized as the ”Low value customer” as shown in the table below −

Syntax

Following is the syntax of SQL CASE statement −

CASE
   WHEN condition1 THEN statement1,
   WHEN condition2 THEN statement2,
   WHEN condition THEN statementN
   ELSE result
END;

Where, condition1, condition2, etc. Are the conditional statements and statement1, statement2, etc.. are the actions to be taken when the condition is true.

Once the condition is met, the CASE statement will stop verifying further and it will return the result.

• If none of the conditions are met (TRUE), then it returns the value mentioned in the ELSE clause.

• It returns NULL if the ELSE part is not mentioned and none of the conditions are TRUE.

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

In the following query, we are using multiple WHEN and THEN conditions to the CASE statement along with the ELSE clause.

If the AGE of the customer is greater than 30, it returns Gen X otherwise moves to the further WHEN and THEN conditions. If none of the conditions is matched with the CUSTOMERS table, CASE returns the ”Gen Alpha” value as mentioned in the ELSE part of the query −

SELECT NAME, AGE,
CASE
WHEN AGE > 30 THEN ''Gen X''
WHEN AGE > 25 THEN ''Gen Y''
WHEN AGE > 22 THEN ''Gen Z''
ELSE ''Gen Alpha''
END AS Generation
FROM CUSTOMERS;

Output

The output produced is as follows −

Example

Let us take a look at another query where we want to provide a 25% increment to each customer if the amount is less than 4500 from the CUSTOMERS table previously created −

SELECT *, CASE
WHEN SALARY < 4500 THEN (SALARY + SALARY * 25/100)
END AS INCREMENT FROM CUSTOMERS;

Output

Here, the SQL command checks if the salary is less than 4500. If this condition is satisfied, a new column ”INCREMENT” will contain the values that is equal to salary with 25% of increment.

Since the ELSE part is not mentioned in the above query and none of the conditions are true for few CUSTOMERS, NULL is returned, which shows that they didn”t get any increment.

CASE Statement with ORDER BY Clause

We can use CASE statement with ORDER BY clause. The ORDER BY clause in SQL sorts the result in ascending (default) or descending order.

Example

In this query, the CASE statement is used to sort the results based on either the ”NAME” column or the ”ADDRESS” column, depending on the value of the ”NAME” column. If the ”NAME” column starts with ”K”, the results are sorted by the ”NAME” column; otherwise, the results are sorted by the ”ADDRESS” column −

SELECT * FROM CUSTOMERS
ORDER BY
(CASE
    WHEN NAME LIKE ''k%'' THEN NAME
    ELSE ADDRESS
END);

Output

The result obtained by executing the above query is as shown below −

CASE Statement with GROUP BY Clause

We can also use the CASE statement with GROUP BY clause. The GROUP BY clause in SQL groups the rows that have same values within one or more columns where an aggregate function is applied to produce summaries.

Example

In the following query we are grouping the customers based on their salaries and calculate the sum of the salary for a specified range of customer data.

If the value in SALARY is less than or equal to 4000, the data will be grouped as ”Lowest paid”. If the value is greater than 4000 and less than or equal to 6500, it will be grouped as ”Average paid”. All other values will be grouped as ”Highest paid”. The SUM function is used to calculate the total of the SALARY for each group −

SELECT
   CASE
      WHEN SALARY <= 4000 THEN ''Lowest paid''
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN ''Average paid''
   ELSE ''Highest paid''
      END AS SALARY_STATUS,
   SUM(SALARY) AS Total
   FROM CUSTOMERS
   GROUP BY
   CASE
      WHEN SALARY <= 4000 THEN ''Lowest paid''
      WHEN SALARY > 4000 AND SALARY <= 6500 THEN ''Average paid''
   ELSE ''Highest paid''
END;

Output

Following is the output of the above query −

CASE Statement with WHERE Clause

We can use the CASE statement with the WHERE clause as well. The WHERE clause is used to filter the rows in a table based on a specified condition.

Example

In the following query, the CASE statement is used to return the different designations of the CUSTOMERS based on their AGE. The WHERE clause is used to filter the rows based on the SALARY of the CUSTOMERS −

SELECT NAME, ADDRESS,
   CASE
      WHEN AGE < 25 THEN ''Intern''
      WHEN AGE >= 25 and AGE <= 27 THEN ''Associate Engineer''
      ELSE ''Senior Developer''
   END as Designation
FROM CUSTOMERS
WHERE SALARY >= 2000;

Output

Output of the above query is as follows −

CASE Statement with UPDATE

We can use CASE statement within the UPDATE statement to perform conditional updates on data in a table.

Example

In the following query we are updating the salary of all the customers based on their age.

If the age of the customer is equal to ”25”, their salary will be updated to ”17000”. If the age is equal to ”32”, it will be updated to ”25000”. For the customers with other ages, salaries will be updated to ”12000” −

UPDATE CUSTOMERS
SET SALARY=
CASE AGE
WHEN 25 THEN 17000
WHEN 32 THEN 25000
ELSE 12000
END;

Output

We get the following result. We can observe that the changes have been done in 7 rows −

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

Verification

We can rectify the changes done in the CUSTOMERS table using the below query −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

As we can see in the above table, the SALARY of all the customers has been updated corresponding to their age.

CASE Statement with INSERT

We can also insert the data into MySQL tables with the help of the CASE statement. We need to provide the INSERT INTO statement with column names and VALUES for data insertion.

Example

Here, if the age of the customer is greater than or equal to 25, then the salary will be 23000; otherwise the salary will be 14000 −

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
VALUES (10, ''Viren'', 28, ''Varanasi'',
   CASE
      WHEN AGE >= 25 THEN 23000
      ELSE 14000
   END
);

Output

We get the following result. We can observe that the change has been done in 1 row −

Query OK, 1 row affected (0.01 sec)

Verification

We can rectify the changes done in the CUSTOMERS table using the below query −

SELECT * FROM CUSTOMERS;

The table is displayed as follows −

Most of the times, there is a need to use two or more conditions to filter required records from a table; but sometimes satisfying one of the conditions would be enough. There are also scenarios when you need to retrieve records that do not satisfy the conditions specified. SQL provides logical connectives for this purpose. They are listed below −

• AND − Operator

• OR − Operator

• NOT − Operator

With the help of these logical connectives, one can retrieve records that are required and also create exceptions for the records that are not needed to be retrieved.

The SQL NOT Operator

SQL NOT is a logical operator/connective used to negate a condition or Boolean expression in a WHERE clause. That is, TRUE becomes FALSE and vice versa.

The most common scenario where this operator can be used occurs when there is a specification of what NOT to include in the result table, instead of what to include.

For instance, in an Indian voting system, people younger than 18 years of age are NOT allowed to vote. Therefore, while retrieving the information of all people who are eligible to vote, using the NOT operator, we can create an exception to minors since it is the only specification.

The NOT operator is always used in a WHERE clause so its scope within the clause is not always clear. Hence, a safer option to exactly execute the query is by enclosing the Boolean expression or a subquery by parentheses.

Syntax

Following is the syntax for SQL NOT operator −

NOT [CONDITION or BOOLEAN EXPRESSION];

Example

In the following example, let us first create a table to demonstrate the usage of NOT operator.

Using the query below, we are creating a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc. −

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

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

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

The table will be created as follows −

The SQL query below retrieves all rows from the ”CUSTOMERS” table where the ”SALARY” column is not greater than 2000.00 −

SELECT * FROM CUSTOMERS WHERE NOT (SALARY > 2000.00);

Output

Following is the output of the above query −

SQL NOT Operator with LIKE

The LIKE operator uses wildcards to perform pattern matching on the records of a table before extracting the matched records.

However, to negate this operation (to extract the unmatched records instead), we can use the NOT operator along with LIKE in the form of NOT LIKE keyword.

Example

Using the following query, we are retrieving all rows from the ”CUSTOMERS” table where the ”NAME” column does not start with the letter ”K” −

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

Output

On executing the query above, the table will be displayed as follows −

SQL NOT Operator with IN

The IN operator returns TRUE if the values in a table column belong to a range of numbers specified in the WHERE clause.

To negate this operation, we can use the NOT IN operator instead. With this, the Boolean expression returns TRUE if the records are not present in the given range.

Example

The following SQL query selects all rows from the ”CUSTOMERS” table where the ”AGE” column does not have values 25, 26, or 32 −

SELECT * FROM CUSTOMERS WHERE AGE NOT IN (25, 26, 32);

Output

The result table is displayed as follows −

SQL NOT Operator with IS NULL

The IS NULL operator is used to check whether the records in a table are NULL. If a NULL value is encountered, it returns TRUE; and FALSE otherwise.

Using NOT operator with the IS NULL operator, we can extract all the records that does not contain NULL values.

Example

This SQL query retrieves all rows from the ”CUSTOMERS” table where the ”AGE” column is not null, i.e. it contains valid age values −

SELECT * FROM CUSTOMERS WHERE AGE IS NOT NULL;

Output

The result table is exactly as the original table as it contains no NULL values −

However, if the table contains any NULL values, the rows containing it will be omitted in the resultant table.

SQL NOT Operator with BETWEEN

BETWEEN operator is used to establish a range as a condition. When used with WHERE clause, this operator acts like a Boolean expression. That is, if values of a table column fall in the specified range, TRUE is returned; and FALSE otherwise.

Using NOT BETWEEN operator with WHERE clause will return its negation. That is, if values of a table column fall in the specified range, FALSE is returned; and TRUE otherwise.

Example

With the given query below, we are displaying records in the CUSTOMERS table whose salary does not fall between 1500.00 and 2500.00 −

SELECT * FROM CUSTOMERS
WHERE SALARY NOT BETWEEN 1500.00 AND 2500.00;

Output

The resultant table is as follows −

SQL NOT Operator with EXISTS

The EXISTS operator works similar to the IN operator; it compares the table records with the specified range in the WHERE clause. However, the IN operator cannot compare the NULL records with the range while EXISTS does.

The NOT EXISTS operator is used to negate this operation.

Example

In the following example, let us create another table Orders to help in demonstrating the usage of NOT operator with EXISTS operator −

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 query is used to print the IDs of customers in CUSTOMERS table that do not exist in the ORDERS table −

SELECT * FROM CUSTOMERS WHERE NOT EXISTS (
SELECT CUSTOMER_ID FROM ORDERS
WHERE ORDERS.CUSTOMER_ID = CUSTOMERS.ID);

Output

The output obtained after executing the query is as follows −

The SQL NOT EQUAL Operator

The SQL NOT EQUAL operator is used to compare two values and return true if they are not equal. It is represented by “<>” and “!=”. The difference between these two is that <> follows the ISO standard, but != doesn”t. So, it is recommended to use the <> operator.

We can use the NOT EQUAL operator in WHERE clause to filter records based on a specific condition and in GROUP BY clause to group the results.

The comparison is case-sensitive by default, while using the NOT EQUAL operator with text values.

Syntax

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

WHERE expression1 <> expression2;

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 −

NOT EQUAL with Text

We can use the NOT EQUAL operator with text in SQL to compare two text values and return. We can use “<>” or “!=” in the WHERE clause of a SQL statement and exclude rows that match a specific text value.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table whose NAME is not ”Ramesh” −

SELECT * FROM CUSTOMERS WHERE NAME <> ''Ramesh

Output

The output of the above code is as shown below −

NOT EQUAL with GROUP BY Clause

We can use the NOT EQUAL operator with the GROUP BY clause to group the results by the values that are not equal to the specified text value.

The aggregate functions such as COUNT(), MAX(), MIN(), SUM(), and AVG() are frequently used with the GROUP BY statement.

Example

Here, we are retrieving the number of records with distinct ages (excluding ”22”) in the ”CUSTOMERS” table and grouping them by age value −

SELECT COUNT(ID), AGE FROM CUSTOMERS
WHERE AGE <> ''22'' GROUP BY AGE;

Output

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

NOT EQUAL with Multiple Conditions

The not equal operator can also be used with multiple conditions in a WHERE clause to filter out rows that match specific criteria.

Example

Now, we are retrieving all the customers whose salary is either “>2000” or “=2000“. At the same time, the customer must not be from “Bhopal” −

SELECT * FROM CUSTOMERS
WHERE ADDRESS <> ''Bhopal'' AND (SALARY>''2000'' OR SALARY=''2000'');

Output

Following is the output of the above code −

Negating a Condition Using NOT EQUAL

In SQL, the NOT EQUAL operator can also be combined with the NOT Operator to negate a condition. It filters out the rows that meet a specific condition.

Example

In the following query, we are retrieving all rows from the “CUSTOMERS” table where the “SALARY” is equal to ”2000” −

SELECT * FROM CUSTOMERS WHERE NOT SALARY != ''2000

Output

After executing the above code, we get the following output −

Let”s assume a table with NULL values in some of its fields. These fields indicate that no values are present in them. SQL allows users to create new records or modify existing ones without specifying a value for a field. If no value is provided, the field is stored with a NULL value.

In SQL, it is not possible to check NULL values with comparison operators such as =, <, or <>. Instead, we use the IS NULL and IS NOT NULL (negation of NULL values) operators.

The SQL IS NULL Operator

The SQL IS NULL operator is used to check whether a value in a column is NULL. It returns true if the column value is NULL; otherwise false.

The NULL is a value that represents missing or unknown data, and the IS NULL operator allows us to filter for records that contain NULL values in a particular column.

Syntax

Following is the syntax of IS NULL operator −

SELECT column_name1, column_name2, column_name3, ... , column_nameN
FROM table_name
WHERE column_nameN IS 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 −

IS NULL with SELECT Statement

We can use the IS NULL operator with a SELECT statement to filter the records with NULL values.

Example

In the following query, we are retrieving all the records from the CUSTOMERS table where the ADDRESS is null −

SELECT * FROM CUSTOMERS WHERE ADDRESS IS NULL;

Output

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

IS NULL with COUNT() Function

We can also use the IS NULL operator with the COUNT() function in SQL to count the number of records with NULL values in a particular column.

Syntax

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

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

Example

The following query returns the count of records have a blank field (NULL) in SALARY column of the CUSTOMERS table −

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

Output

The output produced is as shown below −

IS NULL with UPDATE Statement

We can use the UPDATE statement with the “IS NULL” operator in SQL to update records with NULL values in a particular column.

Syntax

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

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

Example

In the following query, we are updating the blank (NULL) records of the AGE column to a value of 48 −

UPDATE CUSTOMERS SET AGE = 48 WHERE AGE IS NULL;

Output

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

Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  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 −

IS NULL with DELETE Statement

We can also use the DELETE statement with IS NULL operator to delete records with NULL values in a particular column.

Syntax

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

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

Example

In the following query, we are deleting the blank (NULL) records present in the SALARY column of CUSTOMERS table −

DELETE FROM CUSTOMERS WHERE SALARY IS NULL;

Output

We get the following result −

Query OK, 2 rows affected (0.01 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 −

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 −