SQL - AND OR OPERATORS & TOP, LIMIT, ROWNUM CLAUSE

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:

EXAMPLE TABLE 2:

ID	NAME	AGE	ADDRESS	SALARY
1	RAMESH	23	DELHI	34000
2	ANIL	34	BANGALOR	23000
3	HARDHIK	24	MYSORE	34000
4	KOMAL	23	MYSORE	24000
5	NAVEEN	27	MP	20000
6	AVI	33	DELHI	40000

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

SELECT TOP 3 * FROM CUSTOMERS2;

ID	NAME	AGE	ADDRESS	SALARY
1	RAMESH	23	DELHI	34000
2	ANIL	34	BANGALOR	23000
3	HARDHIK	24	MYSORE	34000

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

SELECT * FROM CUSTOMER limit 3;

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

 SELECT * FROM CUSTOMERS

WHERE ROWNUM <= 3;

ID	NAME	AGE	ADDRESS	SALARY
1	RAMESH	23	DELHI	34000
2	ANIL	34	BANGALOR	23000
3	HARDHIK	24	MYSORE	34000

AND Operator:

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

Syntax:

SELECT column1, column2, columnN

FROM table_name

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

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

Example:

Consider the CUSTOMERS table having the following records:

ID	NAME	AGE	ADDRESS	SALARY
1	RAMESH	23	DELHI	34000
2	ANIL	34	BANGALOR	23000
3	HARDHIL	24	MYSORE	34000
4	KOMAL	23	MYSORE	24000
5	NAVEEN	27	MP	20000
6	AVI	33	DELHI	40000

Following is an example, which would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 30000 AND age is less than 25 years:

 SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 30000 AND age < 25;

This would produce the following result:

ID	NAME	SALARY
1	RAMESH	34000
3	HARDHIK	34000

OR Operator:

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

Syntax:

The basic syntax of OR operator with WHERE clause is as follows:

SELECT column1, column2, columnN

FROM table_name

WHERE [condition1] OR [condition2]...OR [conditionN]

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

Example:

Consider the CUSTOMERS table having the following records:

ID	NAME	AGE	ADDRESS	SALARY
1	RAMESH	23	DELHI	34000
2	ANIL	34	BANGALOR	23000
3	HARDHIL	24	MYSORE	34000
4	KOMAL	23	MYSORE	24000
5	NAVEEN	27	MP	20000
6	AVI	33	DELHI	40000

Following is an example, which would fetch ID, Name and Salary fields from the CUSTOMERS table where salary is greater than 30000 OR age is less than 25 years:

SELECT ID, NAME, SALARY

FROM CUSTOMERS

WHERE SALARY > 30000 OR age < 25;

ID	NAME	SALARY
1	RAMESH	34000
3	HARDHIK	34000
4	KOMAL	24000
6	AVI	40000