SQL Clauses

SQL stands for "Structured Query Language". SQL supports four major tags INSERT, UPDATE, DELETE and WHERE. Here I am explaining some rare tags.

1. TOP:

Top clause is use to get some top records from result of our query. We can retrieve top records in numbers or in percentage.

Ex. If we fire query like, 

“SELECT * FROM user” 

then it will return you all records from user table. In this case if you want to retrieve first 10 records or 25% records of all users then you can use this TOP clause.

Ex. SELECT TOP 10 * FROM user

      SELECT TOP 25 PERCENT FROM user

    

2. Alias:

Alias (AS) is used to give shortcut name to long table names. We can use this alias in from clause.

Ex. If you are running with table names as userSalaryTableOfXyz then writing this name every time will be tedious task for coder. In this case alias can be used as given below,

SELECT u.name from userSalaryTableOfXyz AS u where id<3

3. UNION:

Union is used to combine results of two SELECT clauses. But necessary things in union is that, for both SELECT’s number of columns, data types and order must be same. You can union as given below.

Ex. SELECT name from user where id=1

      UNION

      SELECT name from user where id=2

It will give you distinct results from both SELECT clauses. If you want to get all records then simply replace UNION by UNION ALL

4. Wildcard charlist:

Charlist is use when you want match specific alphabets at specific position. Let’s see an example so that idea will be clear.

Ex. SELECT name FROM user WHERE name LIKE ‘[xyz]%’

It will give you names starting with x or y or z.

Also you can try,

SELECT name FROM user WHERE name LIKE ‘[!xyz]%’

It will give you all name not starting with x or y or z.

5. INDEX:

Indexes are manly use for fast searching or getting fast results from query.  You can apply indexing on particular column of table. But there is disadvantage of indexing. If you are applying indexing the search will be fast but updates will be slower, because when you update table records the indexes are also needs to be updated.

Syntax for creating index is,

Ex. CREATE INDEX xyz on tableAbc (column_name).