Hey all 👋,

Hope you all doing great and today we gonna discuss few MySQL topics that are often ignored or say it advanced.

To whom this blog is intended for? Absolute beginners? Intermediates ? To say it is intended for someone between beginner to Intermediate level, A Basic SQL knowledge is sufficient to understand.

Lets get started 🚀🚀

Table of contents :

• CASE Statements
• IFNULL
• COALESCE
• Stored Procedure
• Window Functions
  • RANK
  • DENSE_RANK
  • ROW_NUMBER

CASE Statements

These are similar to if-else statements, The CASE statement goes through a list of conditions and returns the result corresponding to first condition that is true. If none of conditions are met it returns the result from else statement.

Syntax

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Example

SELECT
(CASE
   WHEN follower_count >= 100 THEN "Celebrity"
   ELSE "Poor Regular User"
END) as "User Type"
FROM user_table

/* No Celebrities are harmed in making of this code */

The above statement returns the user type based on his follower count. If it is more than 100 followers then returns "Celebrity" else returns as "Poor Regular User"

IFNULL

This function takes 2 Arguments, if the first argument is NULL, it returns second argument.

Usually used to replace a NULL value with a default value while displaying the data.

Syntax

IFNULL( column_name, alternate_value )

Example

SELECT IFNULL (amount,0) FROM user_table

If value from amount column is null then 0 is returned in place of null value

COALESCE

This function takes 2 or more arguments and It works similar to IFNULL, but returns the first Non NULL argument among the list of arguments, if every argument is NULL then it returns NULL.

Syntax

COALESCE(argument_1, argument_2...., argument_n)

Example

SELECT COALESCE(phonenumber_1, phonenumber_2, phone number_3) FROM user_table

If phone number 1 is null then it checks for phone number 2, even if it is null then it returns phone number 3

Stored Procedure

A stored procedure is a group of SQL statements that has been created and stored in the database.

It accepts input parameters so that only one procedure can be used on the network by multiple clients using different input data.

Syntax

--For creating procedure
DELIMITER &&
CREATE PROCEDURE procedure_name ([[IN | OUT | INOUT] Parameter_name datatype ]... )
BEGIN
   sql_statements...
END &&
DELIMITER ;
-- The delimiter is used to differentiate between the end of the procedure and the sql statements.

For executing procedure
CALL procedure_name ( arguments... );

Example

--Creating getvalues procedure which prints out data in users_table when called
DELIMITER &&
CREATE PROCEDURE getvalues ()
BEGIN
  SELECT * from users_table; 
END&&
DELIMITER;

-- calling procedure
CALL getvalues();

Window Functions (Available only for MySQL 8 +)

Window functions perform calculations on a set of rows that are related together. These related rows are termed as windows.

They Do not merge the rows like GROUP BY, they preserve the existing data.

Mainly there are 3 Categories of window functions.

• Aggregate Window Functions

  SUM(), MAX(), MIN(), AVG(). COUNT()

• Ranking Window Functions

  RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()

• Value Window Functions

  LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Syntax

window_function ([column1....]) OVER ([ PARTITION BY partition_list ] [ ORDER BY order_list])
-- [] = contents inside square brackets are optional

RANK

This function will assign rank to each row within a partition, if there is a tie between the values then they will be assigned same rank and the next rank will be ( previous rank + no of previous duplicates )

Example rank sequence : 1 2 2 4 5 5 7

Example :

First, lets see what's there in the table

SELECT * FROM student

Now lets execute the RANK query

SELECT *, RANK() OVER (ORDER BY marks DESC) as "Rank" from student

You can now see the result is sorted and every person is given a rank value, though the rank is not continuous at row with name vikram

DENSE_RANK

Did you notice discontinuity in rank values when using RANK? Dense Rank does the same thing as RANK but with no discontinuity in rank values.

Considering the same above example, lets execute dense query and see the result

select *, DENSE_RANK() OVER (ORDER BY marks DESC) as "Dense Rank" from student

You can now observe no discontinuity of rank values at row with name as vikram

ROW_NUMBER

This function is used to give sequential number for each row within its partition.

Lets have a look at the table data before executing the ROW_NUMBER query

SELECT * FROM student

You can see students from different departments, using ROW_NUMBER function we can assign each row a sequential number starting from 1 based on department( dept ) he is from.

SELECT *, ROW_NUMBER() OVER(PARTITION BY dept) AS "row number" FROM student;  

You can observe each row of a particular department is given a sequential row number, this is what ROW_NUMBER function can do.

Good Bye👋

That's it for the blog, these are my 2 cents about some tricky SQL concepts. For more information on concepts, you can refer to javapoint

I hope you got to learn something new and will be able to do better with MySQL. If there are any further questions please feel free to ask.