Useful MySql Commands and Tricks

May 28 2014
GROUP_CONCAT

This function returns a string result with the concatenated non-null values from a group. It returns null if there are no non-null values.

Some time we need comma separate values of a group. For example we need to show the details of a department and name of employees. In this case can simply use GROUP_CONCAT(“employee_name”) with “left join” and “group by department_id” query.

Sample Query:

select d.*, GROUP_CONCAT(“e.employee_name”) as employee_list from department as d left join employee as e on d.department_id = e.department_id group by d.department_id


CASE

The return type of a CASE expression is the compatible aggregated type of all return values, but also depends on the context in which it is used. If used in a string context, the result is returned as a string. If used in a numeric context, the result is returned as a decimal, real, or integer value



EXPLAIN

The EXPLAIN statement can be used to obtain information about how MySQL executes a statement. It is very user at the time of query optimization. EXPLAIN provides additional information and estimates the number of table rows that are filtered by the condition.


EXPLAIN select d.*, GROUP_CONCAT(“e.employee_name”) as employee_list from department as d left join employee as e on d.department_id = e.department_id group by d.department_id