Advanced MySQL Queries

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.

Option 1

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;

Option 2

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!

Example 1:

SELECT field_1, GROUP_CONCAT(field_2 SEPARATOR ',') AS field_2_values 
FROM source
GROUP BY field_1;

Example 2:

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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s