SQL Tips and Tricks

Today we’ll be learning some SQL tips and tricks that could save a lot of time.I’ll be using MYSQL workbench 6.3 for the tutorial. As you know SQL is not case sensitive so query can be written in either of the case.

Below is the table in the database named “new_table”

 

Find the Nth highest value

Below is the query to find the Nth highest value. In the query N can be replaced by any number to find the position of.

SELECT column1 FROM (SELECT column1 FROM new_table ORDER BY column1 DESC LIMIT N) AS secondhighest ORDER BY column1 asc LIMIT 1;

The above query is working as:

a. First it is querying (the query inside the bracket) the ‘column1’ in descending order with the limit set by the user.

b. Then from the derived table it is querying the ‘column1’ in ascending order with the limit 1 which gives the desired result.

 

Query every Nth row

With the following trick we can query every Nth row.

Select * from table_name where column1%N=0

Select * from table_name where column1%2=0;  For every 2nd row

Above query will work if we have any column like column1.

 

See all the tables in the Database

To see all the tables in the database, a simple query will be needed.

Show tables;

See the details of all the columns from a table

To see the details of the columns like names, types etc. following query will be used.

SHOW COLUMNS FROM table_name;

 

Copy the data from one table to other

Some times we need to take the backup of the data. Means to create a backup table of the original table Or just to simply copy data from a table to other. If the columns are same in both the table then the following query will be used.

insert into table2 select * from table1;
here we are copying data to table2 from table1; we can also copy few columns

 

MERGE AND UPDATE STATEMENT

MERGE and Update Statement works as first we’ll select the columns from Table1 and stores in tmpTable and when Column1 matches to both the tables it will update else insert the new values.

MERGE Table2

USING (SELECT Column1,Column2....
FROM  Table1
) tmpTable

ON Table2.Column1 = tmpTable.Column1

WHEN MATCHED THEN

UPDATE SET
Table2.Column1 = tmpTable.Column1,

Table2.Column2 = tmpTable.Column2

WHEN NOT MATCHED THEN

INSERT (Column1,Column2,...........................)

VALUES (tmpTable.Column1,tmptable.Column2,.........);

 

NULL = NULL returns False

Values which doesn’t exist can not be compared. There is great Stack Overflow thread on this check out here. Baron also explained in more detailed about nulls in SQL here.

SQL query Order of Execution

Order Of Execution	Clause	Function
1	FROM	Choose the base data
2	ON	Join Condition
3	JOINS	Joins
4	WHERE	Filter the data
5	GROUP BY	Aggregate the data
6	HAVING	Filter the aggregate data
7	SELECT	Returns the final data
8	DISTINCT	Unique data
9	ORDER BY	Sort the final data
10	TOP/LIMIT/OFFSET	Limit the output data

 

I’ll add more tips and tricks till then keep visiting Analytics Tuts for more tutorials.