Sql Set, Between, In, Comparison Operators – Exists, Except clauses – Datatypes in Sql – Limit Statement – Sql Aliases

By | February 24, 2016

SQL OPERATORS

SQL Operaators such as Conditional(AND,OR,NOT) :- That are use to show the data which is dependent on condition. Syntax :{update table name set column name=value where column name=value and column name =value;} same for or.

AND and OR operators are used with Where clause to make more precise conditions for fetching data from database by combining more than one condition together.

AND operator

AND operator is used to set multiple conditions with Where clause.
Example of AND
Consider the following Emp table

eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
12000
404
Scott
44
10000
405
Tiger
35
9000

SELECT * from Emp WHERE salary < 10000 AND age > 25

The above query will return records where salary is less than 10000 and age greater than 25.

eid
name
age
salary
402
Shane
29
8000
405
Tiger
35
9000

OR operator

OR operator is also used to combine multiple conditions with Where clause. The only difference between AND and OR is their behaviour. When we use AND to combine two or more than two conditions, records satisfying all the condition will be in the result. But in case of OR, atleast one condition from the conditions specified must be satisfied by any record to be in the result.
Example of OR
Consider the following Emp table

eid
name
age
salary
401
Anu
22
5000
402
Shane
29
8000
403
Rohan
34
12000
404
Scott
44
10000
405
Tiger
35
9000

SELECT * from Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater than 10000 or age greater than 25.

402
Shane
29
8000
403
Rohan
34
12000
404
Scott
44
10000
405
Tiger
35
9000

Set Operation in SQL          SQL supports few Set operations to be performed on table data. These are used to get meaningful results from data, under different special conditions.
Union
UNION is used to combine the results of two or more Select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and datatype must be same in both the tables.
Example of UNION
The First table,

ID
Name
1
abhi
2
adam

The Second table,

ID
Name
2
adam
3
Chester

Union SQL query will be,select * from First
UNION
select * from second

The result table will look like,

ID
NAME
1
abhi
2
adam
3
Chester

Union All

This operation is similar to Union. But it also shows the duplicate rows.

Example of Union All
The First table,

ID
NAME
1
abhi
2
adam

The Second table,

ID
NAME
2
adam
3
Chester

Union All query will be like,select * from First
UNION ALL
select * from second

The result table will look like,

ID
NAME
1
abhi
2
adam
2
adam
3
Chester

Intersect

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same. MySQL does not support INTERSECT operator.

Example of Intersect
The First table,

ID
NAME
1
abhi
2
adam

The Second table,

ID
NAME
2
adam
3
Chester

Intersect query will be,

select * from First
INTERSECT
select * from second

The result table will look like

ID
NAME
2
adam

Minus

Minus operation combines result of two Select statements and return only those result which belongs to first set of result. MySQL does not support INTERSECT operator.
Example of Minus
The First table,

ID
NAME
1
abhi
2
adam

The Second table,

ID
NAME
2
adam
3
Chester

Minus query will be,select * from First
MINUS
select * from second

The result table will look like,

ID
NAME
1
abhi

Between OPERATOR :

The BETWEEN operator is used in a WHERE clause to select a range of data between two values.
he BETWEEN operator is used to select values within a range.
SyntaxSELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
For example:

SELECT *
FROM suppliers
WHERE supplier_id BETWEEN 5000 AND 5010;

EXAMPLE – WITH DATE
Next, let’s look at how you would use the BETWEEN condition with Dates. The following date example uses the SQL BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM orders
WHERE order_date BETWEEN TO_DATE ('2003/01/01', 'yyyy/mm/dd')
AND TO_DATE ('2003/12/31', 'yyyy/mm/dd');

EXAMPLE – USING NOT OPERATOR

The SQL BETWEEN condition can also be combined with the SQL NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.
For example:

SELECT *
FROM suppliers
WHERE supplier_id NOT BETWEEN 5000 AND 5500;

This SQL BETWEEN condition example would return all rows where the supplier_id was NOT between 5000 and 5500, inclusive.

In Operator :

The IN operator allows you to specify multiple values in a WHERE clause.
syntax :{ SELECT column_name(s)FROM table_name WHERE column_name IN (value1,value2,…)}The SQL IN condition is used to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

EXAMPLE – WITH CHARACTER

Let’s look at an IN condition example using character values.
The following is a SQL SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM suppliers
WHERE supplier_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This SQL IN condition example would return all rows where the supplier_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the select, all fields from the suppliers table would appear in the result set.

This IN condition example is equivalent to the following SQL statement:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM'
OR supplier_name = 'Hewlett Packard'
OR supplier_name = 'Microsoft';

As you can see, using the SQL IN condition makes the statement easier to read and more efficient.

SELECT * FROM Orders WHERE ProductNumber IN (
SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0)

EXAMPLE – USING NOT OPERATOR

Finally, let’s look at an IN condition example using theNOT operator
For example:

SELECT *
FROM suppliers
WHERE supplier_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
     This SQL IN condition example would return all rows where the supplier_name is neither IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

SQL – ALIASES :

This SQL tutorial explains how to use SQL ALIASES (temporary names for columns or tables) with syntax and examples.
DESCRIPTION

  •  SQL ALIASES can be used to create a temporary name for columns or tables.
  • COLUMN ALIASES are used to make column headings in your result set easier to read.
  • TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).

SYNTAXThe syntax to ALIAS A COLUMN in SQL is:
column_name AS alias_name
OR
The syntax to ALIAS A TABLE in SQL is:
table_name alias_name

Parameters or Arguments

column_name
The original name of the column that you wish to alias.
table_name

The original name of the table that you wish to alias.
alias_nameThe temporary name to assign.

NOTE

  • If the alias_name contains spaces, you must enclose the alias_name in quotes.
  • It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
  • The alias_name is only valid within the scope of the SQL statement.

EXAMPLE – ALIAS A COLUMN
Generally, aliases are used to make the column headings in your result set easier to read. For example, when using the COUNT function, you might alias the result of the COUNT function.

For example:

SELECT department, COUNT(*) AS TOTAL
FROM employees
GROUP BY department;

In this example, we’ve aliased the COUNT(*) field as TOTAL. As a result, TOTAL will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

However, it would have been perfectly acceptable to write this example using quotes as follows:

SELECT department, COUNT(*) AS "TOTAL"
FROM employees
GROUP BY department;

Next, let’s look at an example where we are required to enclose the alias_name in quotes.

For example:

SELECT department, COUNT(*) AS "TOTAL EMPLOYEES"
FROM employees
GROUP BY department;

In this example, we’ve aliased the COUNT(*) field as “TOTAL EMPLOYEES”. Since there are spaces in this alias_name, “TOTAL EMPLOYEES” must be enclosed in quotes.

EXAMPLE – ALIAS A TABLE
When you create an alias on a table, it is either because you plan to list the same table name more than once in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL statement shorter and easier to read.Let’s look at an example of how to alias a table name in SQL.

For example:

SELECT s.supplier_id, s.supplier_name, order_details.order_date
FROM suppliers s
INNER JOIN order_details
ON s.supplier_id = order_details.supplier_id
WHERE s.supplier_id > 5000;

In this example, we’ve created an alias for the suppliers table called s. Now within this SQL statement, we can refer to thesuppliers table as s.

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.

For example, we could modify our example above and create an alias for the order_details table as well.

SELECT s.supplier_id, s.supplier_name, od.order_date
FROM suppliers s
INNER JOIN order_details od
ON s.supplier_id = od.supplier_id
WHERE s.supplier_id > 5000;

Now we have an alias for order_details table called od as well as the alias for the suppliers table called s.
SQL – COMPARISON OPERATORS :
This SQL tutorial explores all of the comparison operators used in SQL to test for equality and inequality, as well as the more advanced operators.

DESCRIPTION
Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQL:

Comparison Operator
Description
=
Equal
<>
Not Equal
!=
Not Equal
>
Greater Than
>=
Greater Than or Equal
<
Less Than
<=
Less Than or Equal
IN ( )
Matches a value in a list
NOT
Negates a condition
BETWEEN
Within a range (inclusive)
IS NULL
NULL value
IS NOT NULL
Non-NULL value
LIKE
Pattern matching with % and _
EXISTS
Condition is met if subquery returns at least one row

Let’s start by explaining the common comparison operators such as the equality operator.

EXAMPLE – EQUALITY OPERATOR

In SQL, you can use the = operator to test for equality in a query.

For example:

SELECT *
FROM suppliers
WHERE supplier_name = 'IBM';

In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to IBM.

EXAMPLE – INEQUALITY OPERATOR

In SQL, there are two ways to test for inequality in a query. You can use either the <> or != operator.

For example, we could test for inequality using the <> operator, as follows:

SELECT *
FROM suppliers
WHERE supplier_name <> 'IBM';

In this example, the SELECT statement would return all rows from the suppliers table where the supplier_name is not equal to IBM.

Or you could also write this query using the != operator, as follows:

SELECT *
FROM suppliers
WHERE supplier_name != 'IBM';

Both of these queries would return the same results.
EXAMPLE – GREATER THAN OPERATOR

You can use the > operator in SQL to test for an expression greater than.

SELECT *
FROM customers
WHERE customer_id > 499;

In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 499. A customer_id equal to 499 would not be included in the result set.

EXAMPLE – GREATER THAN OR EQUAL OPERATOR
In SQL, you can use the >= operator to test for an expression greater than or equal to.

SELECT *
FROM customers
WHERE customer_id >= 499;

In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than or equal to 499. In this case, supplier_id equal to 499 would be included in the result set.

EXAMPLE – LESS THAN OPERATOR
You can use the < operator in SQL to test for an expression less than.

SELECT *
FROM products
WHERE product_id < 10;

In this example, the SELECT statement would return all rows from the products table where the product_id is less than 10. A product_id equal to 10 would not be included in the result set.

EXAMPLE – LESS THAN OR EQUAL OPERATOR
In SQL, you can use the <= operator to test for an expression less than or equal to.

SELECT *
FROM products
WHERE product_id <= 10;

In this example, the SELECT statement would return all rows from the products table where the product_id is less than or equal to 10. In this case, product_id equal to 10 would be included in the result set.


Read

20 Cool funny Websites Kills Your Boredom-2016

18 Cool and Interesting Websites 2016 – Internet

The Art of Writing a Perfect Resume – Allinfi


SQL EXISTS clause:

EXISTS simply tests whether the inner query returns any row. If it does, then the outer query proceeds. If not, the outer query does not execute, and the entire SQL statement returns nothing.

The syntax for EXISTS is:

SELECT “column_name1”
FROM “table_name1”
WHERE EXISTS
(SELECT *
FROM “table_name2”
WHERE “condition”);

Please note that instead of *, you can select one or more columns in the inner query. The effect will be identical.

Let’s use the same example tables:

Table Store_Information

Store_Name
Sales
Txn_Date
Los Angeles
1500
Jan-05-1999
San Diego
250
Jan-07-1999
Los Angeles
300
Jan-08-1999
Boston
700
Jan-08-1999

Table Geography

Region_Name
Store_Name
East
Boston
East
New York
West
Los Angeles
West
San Diego

and we issue the following SQL query:

SELECT SUM(Sales) FROM Store_Information
WHERE EXISTS
(SELECT * FROM Geography
WHERE Region_Name = ‘West’);

We’ll get the following result:
SUM(Sales)
2750

At first, this may appear confusing, because the subquery includes the [region_name = ‘West’] condition, yet the query summed up stores for all regions. Upon closer inspection, we find that since the subquery returns more than 0 row, the EXISTS condition is true, and the condition placed inside the inner query does not influence how the outer query is run.

Difference between EXISTS and IN in SQL?

EXISTS will tell you whether a query returned any results.

eg:
SELECT * FROM Orders o WHERE EXISTS (
SELECT * FROM Products p where p.ProductNumber = o.ProductNumber)IN is used to compare one value to several, and can use literal values, like this:SELECT * FROM Orders WHERE ProductNumber IN (1, 10, 100)

You can also use query results with the IN clause, like this:SELECT * FROM Orders WHERE ProductNumber IN (
SELECT ProductNumber FROM Products WHERE ProductInventoryQuantity > 0)

  • EXISTS is much faster than IN, when the sub-query results is very large.
  • IN is faster than EXISTS, when the sub-query results is very small.

Based on cost optimizer:

  • There is no difference.

DATA TYPES in SQL :

MYSQL has many different data types, separated into three categories: numeric, date and time, and string types.
1. Numeric Data Types :-

  • INT – A normal-sized integer that can be signed or unsigned. If signed, the allowable range is from -2147483648 to 2147483647. If unsigned, the allowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
  • TINYINT – A very small integer that can be signed or unsigned. If signed, the allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. You can specify a width of up to 4 digits.
  • SMALLINT – A small integer that can be signed or unsigned. If signed, the allowable range is from -32768 to 32767. If unsigned, the allowable range is from 0 to 65535. You can specify a width of up to 5 digits.
  • MEDIUMINT – A medium-sized integer that can be signed or unsigned. If signed, the allowable range is from -8388608 to 8388607. If unsigned, the allowable range is from 0 to 16777215. You can specify a width of up to 9 digits.

2. Date and Time Types :-

  • DATE – A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For example, May 15, 1985 would be stored as 1985-05-15.
  • DATETIME – A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 4:30 in the afternoon on December 25th, 1980 would be stored as 1980-12-25 16:30:00.
  • TIMESTAMP – A timestamp between midnight, January 1, 1970 and sometime in 2037. This looks like the previous DATETIME format, only without the hyphens between numbers; 3:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 ( YYYYMMDDHHMMSS ).
  • TIME – Stores the time in HH:MM:SS format.
  • YEAR(M) – Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for example YEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.

3. String Types :-

  • CHAR(M) – A fixed-length string between 1 and 255 characters in length (for example CHAR(5)), right-padded with spaces to the specified length when stored. Defining a length is not required, but the default is 1.
  • VARCHAR(M) – A variable-length string between 1 and 255 characters in length; for example VARCHAR(25). You must define a length when creating a VARCHAR field.
  • BLOB or TEXT – A field with a maximum length of 65535 characters. BLOBs are “Binary Large Objects” and are used to store large amounts of binary data, such as images or other types of files. Fields defined as TEXT also hold large amounts of data; the difference between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and are not case sensitive in TEXT fields. You do not specify a length with BLOB or TEXT.

4. TINYBLOB or TINYTEXT – A BLOB or TEXT column with a maximum length of 255 characters. You do not specify a length with TINYBLOB or TINYTEXT.

5. MEDIUMBLOB or MEDIUMTEXT – A BLOB or TEXT column with a maximum length of 16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.

6. LONGBLOB or LONGTEXT – A BLOB or TEXT column with a maximum length of 4294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.

7. ENUM – An enumeration, which is a fancy term for list. When defining an ENUM, you are creating a list of items from which the value must be selected (or it can be NULL). For example, if you wanted your field to contain “A” or “B” or “C”, you would define your ENUM as ENUM (‘A’, ‘B’, ‘C’) and only those values (or NULL) could ever populate that field.

SQL: EXCEPT OPERATOR

   This SQL tutorial explains how to use the SQL EXCEPT operator with syntax and examples.
DESCRIPTION
The SQL EXCEPT operator is used to return all rows in the first SELECT statement that are not returned by the second SELECT statement. Each SELECT statement will define a dataset. The EXCEPT operator will retrieve all records from the first dataset and then remove from the results all records from the second dataset.Except QueryExplanation: The EXCEPT query will return the records in the blue shaded area. These are the records that exist in Dataset1 and not in Dataset2.

Each SELECT statement within the EXCEPT query must have the same number of fields in the result sets with similar data types.

TIP: The EXCEPT operator is not supported in all SQL databases. It can be used in databases such as SQL Server, PostgreSQL, and SQLite.

For databases such as Oracle, use the MINUS operator to perform this type of query.
SYNTAX

The syntax for the SQL EXCEPT operator is:

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
EXCEPT
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Parameters or Arguments

expression1, expression2, expression_n
The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. These are conditions that must be met for the records to be selected.

NOTE There must be same number of expressions in both SELECT statements.
The corresponding expressions must have the same data type in the SELECT statements. For example: expression1 must be the same data type in both the first and second SELECT statement.
EXAMPLE – WITH SINGLE EXPRESSIONLet’s look at an example of how to use the EXCEPT operator in SQL that returns one field with the same data type.
For example:
SELECT product_id
FROM products
EXCEPT
SELECT product_id
FROM inventory;This EXCEPT operator example returns all product_id values that are in the products table and not in theinventory table. What this means is that if a product_id value existed in the products table and also existed in the inventory table, the product_id value would not appear in the EXCEPT query results.

EXAMPLE – WITH MULTIPLE EXPRESSIONSNext, let’s look at an example of how to use the EXCEPT query in SQL that returns more than one column.

For example:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE last_name = 'Johnson'
EXCEPT
SELECT customer_id, last_name, first_name
FROM customers
WHERE customer_id > 45;

In this EXCEPT example, the query will return the records in the contacts table with a contact_id,last_name, and first_name value that does not match the customer_id, last_name, and first_name value in the customers table.
EXAMPLE – USING ORDER BY

Finally, let’s look at how to use the ORDER BY clause in an EXCEPT query in SQL.

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id < 30
EXCEPT
SELECT company_id, company_name
FROM companies
WHERE state = ‘Florida’
ORDER BY 2;In this EXCEPT example, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we’ve sorted the results by supplier_name / company_name in ascending order, as denoted by the ORDER BY 2.

The supplier_name / company_name fields are in position #2 in the result set.

SQL: IS NOT NULL CONDITION

This SQL tutorial explains how to use the SQL IS NOT NULL condition with syntax and examples.
 
DESCRIPTION
The SQL IS NOT NULL Condition is used to test for a NOT NULL value in SELECT, INSERT, UPDATE, or DELETE statement.
SYNTAXThe syntax for the SQL IS NOT NULL Condition is:
expression IS NOT NULL

Parameters or Arguments

expression
The expression to test for a NOT NULL value.
NOTE

  • If expression is not a NULL value, the condition evaluates to TRUE.
  • If expression is a NULL value, the condition evaluates to FALSE.

EXAMPLE – WITH SELECT STATEMENT

Let’s look at an example of how to use SQL IS NOT NULL in a SQL SELECT statement:
SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This SQL IS NOT NULL example will return all records from the customers table where thecustomer_name does not contain a NULL value.
EXAMPLE – WITH INSERT STATEMENT

Next, let’s look at an example of how to use SQL IS NOT NULL in a SQL INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE account_no IS NOT NULL;This SQL IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a NULL value in the customers table.

EXAMPLE – WITH UPDATE STATEMENT
Next, let’s look at an example of how to use SQL IS NOT NULL in a SQL UPDATE statement:
UPDATE suppliers
SET supplier_name = ‘Apple’
WHERE supplier_name IS NOT NULL;This SQL IS NOT NULL example will update records in the suppliers table where the supplier_name does not contain a NULL value.
EXAMPLE – WITH DELETE STATEMENT

Next, let’s look at an example of how to use SQL IS NOT NULL in a SQL DELETE statement:
DELETE FROM customers
WHERE status IS NOT NULL;

This SQL IS NOT NULL example will delete all records from the customers table where the status does not contain a NULL value.

SQL: IS NULL CONDITION

This SQL tutorial explains how to use the SQL IS NULL condition with syntax and examples.
DESCRIPTION

The SQL IS NULL condition is used to test for a NULL value in a SELECT, INSERT, UPDATE, or DELETE statement.
SYNTAX

The syntax for the SQL IS NULL condition is:
expression IS NULL

Parameters or Arguments

expression

The expression to test for a NULL value.
NOTE

  • If expression is a NULL value, the condition evaluates to TRUE.
  • If expression is not a NULL value, the condition evaluates to FALSE.

EXAMPLE – WITH SELECT STATEMENT

Let’s look at an example of how to use IS NULL in a SELECT statement:
SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

This SQL IS NULL example will return all records from the suppliers table where the supplier_namecontains a NULL value.
EXAMPLE – WITH INSERT STATEMENT

Next, let’s look at an example of how to use SQL IS NULL in an INSERT statement:
INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;

This SQL IS NULL example will insert records into the suppliers table where the city contains a NULL value.
EXAMPLE – WITH UPDATE STATEMENT

Next, let’s look at an example of how to use SQL IS NULL in an UPDATE statement:
UPDATE suppliers
SET supplier_name = ‘Apple’
WHERE supplier_name IS NULL;

This SQL IS NULL example will update records in the suppliers table where the supplier_name contains a NULL value.
EXAMPLE – WITH DELETE STATEMENT

Next, let’s look at an example of how to use SQL IS NULL in a DELETE statement:
DELETE FROM suppliers
WHERE supplier_name IS NULL;

This SQL IS NULL example will delete all records from the suppliers table where the supplier_namecontains a NULL value.

SQL: SELECT LIMIT STATEMENT

This SQL tutorial explains how to use the SELECT LIMIT statement in SQL with syntax and examples.
DESCRIPTION

The SQL SELECT LIMIT statement is used to retrieve records from one or more tables in a database and limit the number of records returned based on a limit value.

TIP: SELECT LIMIT is not supported in all SQL databases.

For databases such as SQL Server or MSAccess, use the SELECT TOP statement to limit your results. The SELECT TOP statement is Microsoft’s proprietary equivalent to the SELECT LIMIT statement.
SYNTAX

The syntax for the SELECT LIMIT statement in SQL is:
SELECT expressions
FROM tables
[WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_rows [ OFFSET offset_value ];

Parameters or Arguments
expressions

The columns or calculations that you wish to retrieve.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.

ORDER BY expression

Optional. It is used in the SELECT LIMIT statement so that you can order the results and target those records that you wish to return. ASC is ascending order and DESC is descending order.

LIMIT number_rows
It specifies a limited number of rows in the result set to be returned based onnumber_rows. For example, LIMIT 10 would return the first 10 rows matching the SELECT criteria. This is where sort order matters so be sure to use an ORDER BY clause appropriately.

OFFSET offset_value
Optional. The first row returned by LIMIT will be determined by offset_value.
EXAMPLE – USING LIMIT KEYWORD

Let’s look at how to use a SELECT statement with a LIMIT clause in SQL.

For example:SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = ‘TechOnTheNet.com’
ORDER BY contact_id DESC
LIMIT 5;

This SQL SELECT LIMIT example would select the first 5 records from the contactstable where the website is ‘TechOnTheNet.com’. Note that the results are sorted bycontact_id in descending order so this means that the 5 largest contact_id values will be returned by the SELECT LIMIT statement.

If there are other records in the contacts table that have a website value of ‘TechOnTheNet.com’, they will not be returned by the SELECT LIMIT statement in SQL.

If we wanted to select the 5 smallest contact_id values instead of the largest, we could change the sort order as follows:

SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = ‘allinfi.com’
ORDER BY contact_id ASC
LIMIT 5;Now the results would be sorted by contact_id in ascending order, so the first 5 smallest contact_id records that have a website of ‘allinfi.com’ would be returned by this SELECT LIMIT statement. No other records would be returned by this query.
EXAMPLE – USING OFFSET KEYWORD

The offset keyword allows you to offset the first record returned by the LIMIT clause. For example:
LIMIT 3 OFFSET 1

This LIMIT clause would return 3 records in the result set with an offset of 1. What this means is that the SELECT statement would skip the first record that would normally be returned and instead return the second, third, and fourth records.

Let’s look at how to use a SELECT LIMIT statement with an OFFSET clause in SQL.

For example:SELECT contact_id, last_name, first_name
FROM contacts
WHERE website = ‘TechOnTheNet.com’
ORDER BY contact_id DESC
LIMIT 5 OFFSET 2;

This SQL SELECT LIMIT example uses an OFFSET of 2 which means that the first and second records in the result set will be skipped…and then the next 5 rows will be returned.

Leave a Reply

Your email address will not be published. Required fields are marked *