SQL Clauses

SQL WHERE

A WHERE clause in SQL is a data manipulation language statement.

WHERE clauses are not mandatory clauses of SQL DML statements. But it can be used to limit the number of rows affected by a SQL DML statement or returned by a query.

Actually, it filters the records. It returns only those queries which fulfill the specific conditions.

WHERE clause is used in SELECT, UPDATE, DELETE statement etc.

Let's see the syntax for sql where:

1.    SELECT column1, column 2, ... column n  

2.    FROM    table_name  

3.    WHERE [conditions]  

WHERE clause uses some conditional selection

=	Equal
>	greater than
<	less than
>=	greater than or equal
<=	less than or equal
< >	not equal to

SQL AND

The SQL AND condition is used in SQL query to create two or more conditions to be met.

It is used in SQL SELECT, INSERT, UPDATE and DELETE statements.

Let's see the syntax for SQL AND:

1.    SELECT columns  

2.    FROM tables  

3.    WHERE condition 1  

4.    AND condition 2;  

The SQL AND condition requires that both conditions should be met.

The SQL AND condition also can be used to join multiple tables in a SQL statement.

SQL "AND" example with "INSERT" statement

This is how an SQL "AND" condition can be used in the SQL INSERT statement.

For example:

1.    INSERT INTO suppliers  

2.    (supplier_id, supplier_name)  

3.    SELECT account_no, name  

4.    FROM customers  

5.    WHERE customer_name ='IBM'  

6.    AND employees =1000;  

SQL "AND" example with "UPDATE" statement

This is how the "AND" condition can be used in the SQL UPDATE statement.

For example:

1.    UPDATE suppliers  

2.    SET supplier_name = 'HP'  

3.    WHERE supplier_name = 'IBM'  

4.    AND offices = 8;  

SQL "AND" example with "DELETE" statement

This is how an SQL "AND" condition can be used in the SQL DELETE statement.

For example:

1.    DELETE FROM suppliers  

2.    WHERE supplier_name = 'IBM'  

3.    AND product = 'PC computers';  

SQL OR

The SQL OR condition is used in a SQL query to create a SQL statement where records are returned when any one of the condition met. It can be used in a SELECT statement, INSERT statement, UPDATE statement or DELETE statement.

Let's see the syntax for OR condition:

1.    SELECT columns  

2.    FROM tables  

3.    WHERE condition 1  

4.    OR condition 2;  

SQL "OR" example with SQL SELECT

1.    SELECT *   

2.    FROM suppliers  

3.    WHERE city = 'New York'  

4.    OR available_products >= 250;  

SQL "OR" example with SQL INSERT

You can see in below example that how an SQL "OR" condition is used with SQL insert statement.

For example:

1.    INSERT INTO suppliers(supplier_id, supplier_name)  

2.    SELECT account_no, name  

3.    FROM customers  

4.    WHERE city = 'New Delhi'  

5.    OR city = 'Ghaziabad';  

SQL "OR" example with SQL UPDATE

For example:

1.    UPDATE suppliers   

2.    SET supplier_name = 'HP'  

3.    WHERE supplier_name = 'IBM'  

4.    OR available_product >36;  

SQL "OR" example with SQL DELETE

For example:

1.    DELETE FROM suppliers  

2.    WHERE supplier_name = 'IBM'  

3.    OR employee <=100;  

 

 

SQL BETWEEN

The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

You can use BETWEEN clause to replace a combination of "greater than equal AND less than equal" conditions.

SQL BETWEEN Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

To understand BETWEEN clause, consider an employee_tbl table, which is having following records:

mysql> SELECT * FROM employee_tbl;

+------+------+------------+--------------------+

| id   | name | work_date  | daily_typing_pages |

+------+------+------------+--------------------+

|    1 | John | 2007-01-24 |                250 |

|    2 | Ram  | 2007-05-27 |                220 |

|    3 | Jack | 2007-05-06 |                170 |

|    3 | Jack | 2007-04-06 |                100 |

|    4 | Jill | 2007-04-06 |                220 |

|    5 | Zara | 2007-06-06 |                300 |

|    5 | Zara | 2007-02-06 |                350 |

+------+------+------------+--------------------+

7 rows in set (0.00 sec)

Now, suppose based on the above table you want to fetch records with conditions daily_typing_pages more than 170 and equal and less than 300 and equal. This can be done using >= and <= conditions as follows:

mysql>SELECT * FROM employee_tbl 

    ->WHERE daily_typing_pages >= 170 AND

    ->daily_typing_pages <= 300;

+------+------+------------+--------------------+

| id   | name | work_date  | daily_typing_pages |

+------+------+------------+--------------------+

|    1 | John | 2007-01-24 |                250 |

|    2 | Ram  | 2007-05-27 |                220 |

|    3 | Jack | 2007-05-06 |                170 |

|    4 | Jill | 2007-04-06 |                220 |

|    5 | Zara | 2007-06-06 |                300 |

+------+------+------------+--------------------+

5 rows in set (0.03 sec)

Same can be achieved using BETWEEN clause as follows:

mysql> SELECT * FROM employee_tbl 

    -> WHERE daily_typing_pages BETWEEN 170 AND 300; 

+------+------+------------+--------------------+

| id   | name | work_date  | daily_typing_pages |

+------+------+------------+--------------------+

|    1 | John | 2007-01-24 |                250 |

|    2 | Ram  | 2007-05-27 |                220 |

|    3 | Jack | 2007-05-06 |                170 |

|    4 | Jill | 2007-04-06 |                220 |

|    5 | Zara | 2007-06-06 |                300 |

+------+------+------------+--------------------+

 

NOT BETWEEN Operator Example

To display the products outside the range of the previous example, use NOT BETWEEN:

Example

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

BETWEEN Operator with IN Example

The following SQL statement selects all products with a price BETWEEN 10 and 20, but products with a CategoryID of 1,2, or 3 should not be displayed:

Example

SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);

NOT BETWEEN Operator with Text Value Example

The following SQL statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'C' and 'M':

Example

SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';

BETWEEN Operator with Date Value Example

The following SQL statement selects all orders with an OrderDate BETWEEN '04-July-1996' and '09-July-1996':

Example

SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;

SQL - LIKE Clause

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator:

·        The percent sign (%)

·        The underscore (_)

The percent sign represents zero, one, or multiple characters. The underscore represents a single number or character. The symbols can be used in combinations.

Syntax:

The basic syntax of % and _ is as follows:

SELECT FROM table_name

WHERE column LIKE 'XXXX%'

or

SELECT FROM table_name

WHERE column LIKE '%XXXX%'

or

SELECT FROM table_name

WHERE column LIKE 'XXXX_'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX'

or

SELECT FROM table_name

WHERE column LIKE '_XXXX_'

You can combine N number of conditions using AND or OR operators. Here, XXXX could be any numeric or string value.

Example:

Here are number of examples showing WHERE part having different LIKE clause with '%' and '_' operators:

Statement	Description
WHERE SALARY LIKE '200%'	Finds any values that start with 200
WHERE SALARY LIKE '%200%'	Finds any values that have 200 in any position
WHERE SALARY LIKE '_00%'	Finds any values that have 00 in the second and third positions
WHERE SALARY LIKE '2_%_%'	Finds any values that start with 2 and are at least 3 characters in length
WHERE SALARY LIKE '%2'	Finds any values that end with 2
WHERE SALARY LIKE '_2%3'	Finds any values that have a 2 in the second position and end with a 3
WHERE SALARY LIKE '2___3'	Finds any values in a five-digit number that start with 2 and end with 3

Let us take a real example, consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  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 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

Following is an example, which would display all the records from CUSTOMERS table where SALARY starts with 200:

SQL> SELECT * FROM CUSTOMERS

WHERE SALARY LIKE '200%';

This would produce the following result:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

SQL Wildcards 

The SQL wildcards can be used to search data within a table.

SQL wildcards are used with SQL LIKE operator.

The boolean NOT operator in the select statement can be used as wildcard NOT LIKE operator.

In SQL, the wildcards are :

Wildcards	Description
%	The percent sign character (%) represent a sequence of 0 (zero) or more characters.
Underscore ( _ )	The underscore character ( _ ) represents a single character.
[charlist]	It represents any single character within a charlist
[^charlist] or [!charlist]	It represents any single character other than the charlist

 

 

SQL - TOP, LIMIT or ROWNUM Clause

The SQL TOP clause is used to fetch a TOP N number or X percent records from a table.

Note: All the databases do not support TOP clause. For example MySQL supports LIMIT clause to fetch limited number of records and Oracle uses ROWNUM to fetch limited number of records.

Syntax:

The basic syntax of TOP clause with SELECT statement would be as follows:

SELECT TOP number|percent column_name(s)

FROM table_name

WHERE [condition]

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  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 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

Following is an example on SQL server, which would fetch top 3 records from CUSTOMERS table:

SQL> SELECT TOP 3 * FROM CUSTOMERS;

This would produce the following result:

+----+---------+-----+-----------+---------+

| ID | NAME    | AGE | ADDRESS   | SALARY  |

+----+---------+-----+-----------+---------+

|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |

|  2 | Khilan  |  25 | Delhi     | 1500.00 |

|  3 | kaushik |  23 | Kota      | 2000.00 |

+----+---------+-----+-----------+---------+

If you are using MySQL server, then here is an equivalent example:

SQL> SELECT * FROM CUSTOMERS

LIMIT 3;

This would produce the following result:

+----+---------+-----+-----------+---------+

| ID | NAME    | AGE | ADDRESS   | SALARY  |

+----+---------+-----+-----------+---------+

|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |

|  2 | Khilan  |  25 | Delhi     | 1500.00 |

|  3 | kaushik |  23 | Kota      | 2000.00 |

+----+---------+-----+-----------+---------+

If you are using Oracle server, then here is an equivalent example:

SQL> SELECT * FROM CUSTOMERS

WHERE ROWNUM <= 3;

This would produce the following result:

+----+---------+-----+-----------+---------+

| ID | NAME    | AGE | ADDRESS   | SALARY  |

+----+---------+-----+-----------+---------+

|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |

|  2 | Khilan  |  25 | Delhi     | 1500.00 |

|  3 | kaushik |  23 | Kota      | 2000.00 |

+----+---------+-----+-----------+---------+

---

SQL - Having

The HAVING clause enables you to specify conditions that filter which group results appear in the final results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax:

The following is the position of the HAVING clause in a query:

SELECT

FROM

WHERE

GROUP BY

HAVING

ORDER BY

The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following is the syntax of the SELECT statement, including the HAVING clause:

SELECT column1, column2

FROM table1, table2

WHERE [ conditions ]

GROUP BY column1, column2

HAVING [ conditions ]

ORDER BY column1, column2

Example:

Consider the CUSTOMERS table having the following records:

+----+----------+-----+-----------+----------+

| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  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 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

Following is the example, which would display record for which similar age count would be more than or equal to 2:

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY

FROM CUSTOMERS

GROUP BY age

HAVING COUNT(age) >= 2;

This would produce the following result:

+----+--------+-----+---------+---------+

| ID | NAME   | AGE | ADDRESS | SALARY  |

+----+--------+-----+---------+---------+

|  2 | Khilan |  25 | Delhi   | 1500.00 |

+----+--------+-----+---------+---------+