In MySQL how can I write this SQL query in the most efficient way?

Question

CREATE TABLE `locationcodes` (
  `id` int,
  `customer` varchar(100),
  `locationcode` varchar(50),
  `parentid` int
);

insert into locationcodes values (1, 'Test, Inc.', 'California', NULL);
insert into locationcodes values (2, 'Test, Inc.', 'Los Angeles', 1);
insert into locationcodes values (3, 'Test, Inc.', 'San Francisco', 1);
insert into locationcodes values (4, 'Test, Inc.', 'Sacramento', 1);

I desire a list of parent locations and their children. If no child, then print parent parent:

SQL:

SELECT DISTINCT parent.locationcode as 'Parent', parent.locationcode as 'Child', 1 AS `level`
FROM locationcodes parent
JOIN locationcodes child ON parent.id = child.parentid
WHERE parent.parentid IS NULL
AND parent.customer = 'Test, Inc.'
UNION
SELECT DISTINCT parent.locationcode as 'Parent', child.locationcode as 'Child', 2 AS `level`
FROM locationcodes parent 
JOIN locationcodes child ON parent.id = child.parentid
WHERE NOT child.parentid IS NULL
AND child.customer = 'Test, Inc.'
ORDER BY 1, 2

Results are correct:

PARENT          CHILD           LEVEL
California      California          1
California      Los Angeles         2
California      Sacramento          2
California      San Francisco       2

My question is did I write the SQL efficiently as possible?

http://sqlfiddle.com/#!2/87a3d/3

You don't need `WHERE NOT child.parentid IS NULL`. When it's null, `parent.id = child.parentid` will not match. — Barmar, Apr 10 '14 at 23:42
This question appears to be off-topic because questions about improving working code belong on codereview.stackexchange.com. — Barmar, Apr 10 '14 at 23:43
@Barmar, If poster doesnt provide his own attempt at a solution, then people complain he is lazy. But if he does provide code, then he is told to move it to code review! — davidjhp, Apr 10 '14 at 23:48
the difference is whether the code is working or non-working. SO is for help fixing broken code, CR is for help improving working code. — Barmar, Apr 10 '14 at 23:48
You might want to omit *efficiently as possible* as that will incite the masses here to a whole lot of fanaticism about optimization. What you probably mean is *Is this a reasonable query?* — wallyk, Apr 10 '14 at 23:56
You might like my answer to [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree/192462#192462) — Bill Karwin, Apr 11 '14 at 00:21

S.B. · Accepted Answer · 2014-04-11T00:07:28.103

How about using just a single JOIN and some inline IFs?

SELECT IFNULL(parent.locationcode,child.locationcode) AS 'Parent',  child.locationcode AS 'Child', IF(child.parentid,2,1) AS `level`
FROM locationcodes child 
LEFT JOIN locationcodes parent ON parent.id = child.parentid
WHERE child.customer = 'Test, Inc.'
ORDER BY 1, 2

This selects all (child) locations and then tries to join parent data where possible. Should be a lot more efficient (and simple!) than using UNION with another JOIN.

Demo: http://sqlfiddle.com/#!2/87a3d/21/0

In MySQL how can I write this SQL query in the most efficient way?

1 Answers1