Over my years of working with MySQL databases and writing hundreds of queries for various applications; I have come across several trick and under utilized functions that can help you out in unique situations. Please note, some of these queries are rather advances if you are new to MySQL so please be careful when playing with these scripts.
Some of these features have multiple ways you can achieve them and I have tried to include them as well. I don’t go into where any why you might want to use these different options, but I am assuming if you are reading this you can probably figure it out.
Join On USING()
When you create a database that utilizes descriptive names and you name your foreign keys the same between your tables you can utilize the USING function when you JOIN tables.
SELECT a.*, b.* FROM source_1 a JOIN source_2 b USING(fieldId);
* Please note, this doesn’t always work when doing multiple joins on the same column name. The USING function does not accept table specific columns.
Join Table with the Same Table
Sometimes when creating a database you will utilize a table for multiple types of items. This snippet allows you to count child rows from within the same table.
SELECT a.*, COUNT(*) as child_count FROM source a JOIN source b on b.parent_id = a.id WHERE b.parent_id IS NOT NULL GROUP BY b.parent_id ORDER_BY child_count DESC;
SELECT a.*, b.child_count FROM source a JOIN ( SELECT parent_id, COUNT(id) AS child_count FROM source WHERE parent_id IS NOT null GROUP BY parent_id ) b ON b.parent_id = a.id ORDER BY b.child_count DESC;
Select Where Field in a Separate Table
When using database’s a lot of time tables are dependent on other tables. Using this snippet let’s you only select rows where a column matches a column in another table.
SELECT * FROM source_1 WHERE type IN ( SELECT type FROM source_2 WHERE active = 1 );
Selecting Columns from 2 Tables Without a Join
This is a handy little function that lets you call 2 tables and join their outputs as long as the number of columns and the column names match.
SELECT source_1_field AS field FROM source_1 UNION SELECT source_2_field AS field FROM source_2
Select a String of Values from Multiple Rows
Sometimes you need to get a list of values returned in a single row. With this code snippet you can do just that!
SELECT field_1, GROUP_CONCAT(field_2 SEPARATOR ',') AS field_2_values FROM source GROUP BY field_1;
SELECT a.field_1, GROUP_CONCAT(DISTINCT b.field_2 ORDER BY b.field_2 DESC SEPARATOR ',') AS field_2_values FROM source_1 a JOIN source_2 b USING(field_1) GROUP BY b.field_1;