This post comes from the first version of this blog.
Please send me an email if anything needs an update. Thanks!

W ciągu ostatnich tygodni naprawdę mocno pracowałem z przeróżnymi zapytaniami SQL, stąd też wynotowałem sobie kilka kwestii, które uznałem za warte opisania na blogu. Ostatnie dwa wpisy na ten temat, czyli znajdowanie rekordów sąsiednich, czy też operacje na przedziałach czasu to tylko wierzchołek góry lodowej problemów, z jakimi musiałem się zmierzyć, żeby osiągnąć zamierzony efekt. Dzisiaj chciałbym powiedzieć kilka słów na temat składni zapytań łączonych operatorem UNION - zapraszam do lektury.

Fotografia: BrentOzar, CC-BY-SA.

(My)SQL: Klauzule ORDER BY oraz LIMIT w zapytaniach z operatorem UNION.

Weźmy pod uwagę dwie tabele: 
1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdBy` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

 

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS `wpisy` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `createdBy` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `description` text,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Pewnie się zastanawiacie, po co dwie tabele o tych samych polach - nie można było zrobić jednej? Otóż, właśnie jedno z moich zadań w pracy polegało na tym, że miałem dwie tabele o bardzo zbliżonej zawartości, a musiałem zintegrować tą drugą z elementem pobierającym dane tylko z pierwszej. Mogłem oczywiście zrobić dwa zapytania i potem kombinować z pętlami w PHP, ale po co, skoro mamy SQLa? ;]

Mamy więc przykładowe zapytanie:

1
2
3
4
5

SELECT a.id, a.name, a.description
FROM articles a
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name

Załóżmy, że nasze zadanie polega na wyświetleniu 20 rekordów z bazy danych, gdzie pierwsze 10 będzie pochodzić z pierwszej tabeli, a drugie 10 z drugiej, przy czym sortowanie ma się odbywać po nazwie w ramach obu podgrup (oddzielnie posortowana pierwsza i druga dziesiątka elementów). Spróbujmy skorzystać z następującego zapytania:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT a.id, a.name, a.description
FROM articles a
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name

UNION

SELECT w.id, w.name, w.description
FROM wpisy w
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name

Czy wynik będzie zawierał 20 rekordów (zakładamy, że w tych tabelach jest odpowiednia ilość danych)? Niestety nie. Czy dane będą posortowane grupami? Też nie. Jak więc zatem zmusić bazę danych do posłuszeństwa i zwrócenia 20 rekordów w odpowiedniej kolejności? Musimy zamknąć podzapytania złączone operatorem UNION w nawiasach i wtedy wszystko zadziała bez problemu:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

(SELECT a.id, a.name, a.description
FROM articles a
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name)

UNION

(SELECT w.id, w.name, w.description
FROM wpisy w
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name)

Dlaczego tak się dzieje? Ponieważ ostatnie klauzule LIMIT oraz ORDER BY w zapytaniach bez nawiasów odnoszą się do całości, a nie tylko do drugiego członu operatora UNION. Krótko mówiąc, sytuacja jest dokładnie taka, jak w zapytaniu poniżej:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

(SELECT a.id, a.name, a.description
FROM articles a
WHERE createdBy = 5
LIMIT 10
ORDER BY a.name)

UNION

(SELECT w.id, w.name, w.description
FROM wpisy w
WHERE createdBy = 5)
LIMIT 10
ORDER BY a.name

Jeśli zamkniemy podzapytanie w nawiasach, to żadna jego część podstępnie nie wymknie się nam spod kontroli i zwróci odpowiedni zbiór danych, dokładnie tak, jak tego oczekujemy.

Oczywiście możemy też celowo umieścić jedną lub więcej klauzul pod całym zapytaniem, formując np. taki kod:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

(SELECT a.id, a.name, a.description
FROM articles a
WHERE createdBy = 5
LIMIT 10)

UNION

(SELECT w.id, w.name, w.description
FROM wpisy w
WHERE createdBy = 5
LIMIT 10)

ORDER BY a.name

Dostaniemy wtedy 20 rekordów, posortowanych po nazwie w całej przestrzeni zwróconego zbioru danych.

Być może tego typu problemy są proste, ale można się na nich nieco “przejechać” - szczególnie, jeśli pracuje się z bardzo starym kodem, który “ktoś gdzieś kiedyś” napisał i teraz trzeba go zmienić. Mam nadzieję, że moje dzisiejsze dywagacje przydadzą się Wam, a co najmniej przypomną o konieczności stosowania bardzo “wyraźnych” zapisów w kodzie, bo komputer raczej nie będzie się domyślał, tylko zwyczajnie “zrobi po swojemu”.