Скачать или смотреть How to Join Labels and Values in MySQL while Including Not Referenced Labels as NULL

Learn how to effectively join two tables in MySQL to include all labels, even those without references, and return NULL values where applicable.
---
This video is based on the question https://stackoverflow.com/q/62480224/ asked by the user 'thenubian' ( https://stackoverflow.com/u/12091707/ ) and on the answer https://stackoverflow.com/a/62480242/ provided by the user 'GMB' ( https://stackoverflow.com/u/10676716/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Mysql - joining labels and values table and including not referenced labels as null

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

If you're working with databases in MySQL, you might often need to combine data from multiple tables. A common scenario is when you have tables that hold different types of data, such as labels and values.

Imagine you have a scenario with a labels table that categorizes various options, and a values table that holds specific records referencing those options. The challenge presented here is how to create a query that combines these two tables and includes labels—even when there are no corresponding records in the values table—resulting in NULL entries for those labels.

In this post, we’ll break down the solution to this problem using a specific example.

The Setup

We have two tables in the database:

Options Table: This table stores the available labels.

[[See Video to Reveal this Text or Code Snippet]]

Records Table: This contains records associated with certain options.

[[See Video to Reveal this Text or Code Snippet]]

The objective is to join these two tables in a way that retrieves all the options for each profession, displaying NULL values for those not referenced in the records.

The Solution

To achieve this, we will follow these steps:

Identify Distinct Professions: First, we need to get the unique professions available in the Records table.

Cross Join Options: Next, we will perform a cross join with the options table to generate all possible combinations of professions and options.

Left Join with Records: Finally, we will use a left join to bring the actual records into our query while still ensuring that all combinations of professions and options are displayed, including those with NULLs for first names where there are no matches.

SQL Query

Here is the SQL query that accomplishes this:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the SQL Query

SELECT p.profession, o.option_name, r.first_name: This part selects the columns that we'd like to display in our results.

FROM (SELECT DISTINCT profession FROM records) p: This nested query gets the unique professions from the records table, which we alias as p.

CROSS JOIN options o: This performs the cross join of the options table to ensure that all labels are considered.

LEFT JOIN records r ON r.option_id = o.option_id AND r.profession = p.profession: The left join links the records table to our generated combinations, allowing NULL where no records exist.

ORDER BY p.profession, o.option_name: Finally, we order the output by profession and option name for better readability.

Conclusion

With this simple yet powerful approach, you can effectively join labels and values in MySQL, ensuring that all potential options are displayed—even if there are no actual records to reference. This method is invaluable in scenarios where it is important to see all categories, alongside their corresponding records.

Give this query a try in your own MySQL database and see how it can simplify your data representation!