MySQL ON vs USING?

W MySQL JOIN, jaka jest różnica między ONi USING()? O ile mogę stwierdzić, USING()jest to po prostu wygodniejsza składnia, a jednocześnie ONdaje nieco większą elastyczność, gdy nazwy kolumn nie są identyczne. Jednak różnica ta jest tak niewielka, że ​​można by się po prostu pozbyć USING().

Czy jest w tym coś więcej niż na pierwszy rzut oka? Jeśli tak, które powinienem zastosować w danej sytuacji?

Jest to głównie cukier syntaktyczny, ale na uwagę zasługuje kilka różnic:

ON jest bardziej ogólnym z dwóch. Można łączyć tabele NA kolumnie, zestaw kolumn, a nawet warunek. Na przykład:

SELECT * FROM world.City JOIN world.Country ON (City.CountryCode = Country.Code) WHERE ...

UŻYWANIE jest przydatne, gdy obie tabele mają wspólną kolumnę o tej samej nazwie, do której dołączają. W takim przypadku można powiedzieć:

SELECT ... FROM film JOIN film_actor USING (film_id) WHERE ...

Dodatkową przyjemnością jest to, że nie trzeba w pełni kwalifikować łączących się kolumn:

SELECT film.title, film_id -- film_id is not prefixed
FROM film
JOIN film_actor USING (film_id)
WHERE ...

Aby to zilustrować, aby zrobić powyższe z ON , musielibyśmy napisać:

SELECT film.title, film.film_id -- film.film_id is required here
FROM film
JOIN film_actor ON (film.film_id = film_actor.film_id)
WHERE ...

Zwróć uwagę na film.film_idkwalifikację w SELECTklauzuli. Powiedzenie byłoby niewłaściwe, film_idponieważ spowodowałoby to dwuznaczność:

BŁĄD 1052 (23000): Kolumna „film_id” na liście pól jest niejednoznaczna

Jeśli chodzi o select *, kolumna łącząca pojawia się w zestawie wyników dwa razy za pomocą, ONa pojawia się tylko raz za pomocą USING:

mysql> create table t(i int);insert t select 1;create table t2 select*from t;
Query OK, 0 rows affected (0.11 sec)

Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.19 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select*from t join t2 on t.i=t2.i;
+------+------+
| i    | i    |
+------+------+
|    1 |    1 |
+------+------+
1 row in set (0.00 sec)

mysql> select*from t join t2 using(i);
+------+
| i    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql>

Pomyślałem, że przydałbym się tutaj, gdy okazałem ONsię bardziej przydatny niż USING. To wtedy OUTERsprzężenia są wprowadzane do zapytań.

ONkorzysta z możliwości ograniczenia zestawu wyników tabeli, do której OUTERprzyłącza się zapytanie, przy jednoczesnym zachowaniu OUTERłączenia. Próba ograniczenia zestawu wyników poprzez określenie WHEREklauzuli skutecznie zmieni OUTERzłączenie w INNERzłączenie.

Oczywiście może to być względny przypadek narożny. Warto się tam jednak wystawić .....

Na przykład:

CREATE TABLE country (
   countryId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
   country varchar(50) not null,
  UNIQUE KEY countryUIdx1 (country)
) ENGINE=InnoDB;

insert into country(country) values ("France");
insert into country(country) values ("China");
insert into country(country) values ("USA");
insert into country(country) values ("Italy");
insert into country(country) values ("UK");
insert into country(country) values ("Monaco");


CREATE TABLE city (
  cityId int(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  countryId int(10) unsigned not null,
  city varchar(50) not null,
  hasAirport boolean not null default true,
  UNIQUE KEY cityUIdx1 (countryId,city),
  CONSTRAINT city_country_fk1 FOREIGN KEY (countryId) REFERENCES country (countryId)
) ENGINE=InnoDB;


insert into city (countryId,city,hasAirport) values (1,"Paris",true);
insert into city (countryId,city,hasAirport) values (2,"Bejing",true);
insert into city (countryId,city,hasAirport) values (3,"New York",true);
insert into city (countryId,city,hasAirport) values (4,"Napoli",true);
insert into city (countryId,city,hasAirport) values (5,"Manchester",true);
insert into city (countryId,city,hasAirport) values (5,"Birmingham",false);
insert into city (countryId,city,hasAirport) values (3,"Cincinatti",false);
insert into city (countryId,city,hasAirport) values (6,"Monaco",false);

-- Gah. Left outer join is now effectively an inner join 
-- because of the where predicate
select *
from country left join city using (countryId)
where hasAirport
; 

-- Hooray! I can see Monaco again thanks to 
-- moving my predicate into the ON
select *
from country co left join city ci on (co.countryId=ci.countryId and ci.hasAirport)
; 

Wikipedia zawiera następujące informacje na temat USING:

Konstrukt USING jest jednak czymś więcej niż zwykłym cukrem syntaktycznym, ponieważ zestaw wyników różni się od zestawu wyników wersji z wyraźnym predykatem. W szczególności wszelkie kolumny wymienione na liście USING pojawią się tylko raz, z niekwalifikowaną nazwą, a nie raz dla każdej tabeli w złączeniu. W powyższym przypadku będzie istniała pojedyncza kolumna DepartmentID i brak pracownika.DepartmentID lub departament.DepartmentID.

Tabele, o których mówiono:

Dokumentacja Postgres również definiuje je całkiem dobrze:

Klauzula ON jest najbardziej ogólnym rodzajem warunku złączenia: przyjmuje wyrażenie logiczne tego samego rodzaju, co używane w klauzuli WHERE. Para wierszy z T1 i T2 jest zgodna, jeśli wyrażenie ON ma wartość true.

Klauzula USING jest skrótem, który pozwala skorzystać z konkretnej sytuacji, w której obie strony złączenia używają tej samej nazwy dla kolumn łączących. Pobiera rozdzieloną przecinkami listę wspólnych nazw kolumn i tworzy warunek łączenia, który obejmuje porównanie równości dla każdej z nich. Na przykład połączenie T1 i T2 za pomocą USING (a, b) daje warunek połączenia ON T1.a = T2.a AND T1.b = T2.b.

Co więcej, wynik JOIN USING pomija zbędne kolumny: nie ma potrzeby drukowania obu dopasowanych kolumn, ponieważ muszą one mieć równe wartości. Podczas gdy JOIN ON tworzy wszystkie kolumny z T1, a następnie wszystkie kolumny z T2, JOIN USING tworzy jedną kolumnę wyjściową dla każdej z wymienionych par kolumn (w podanej kolejności), a następnie wszystkie pozostałe kolumny z T1, a następnie wszelkie pozostałe kolumny z T2 .

Dla eksperymentujących z tym w phpMyAdminie, tylko słowo:

Wygląda na to, że phpMyAdmin ma kilka problemów USING. Dla przypomnienia jest to phpMyAdmin działający na Linux Mint, wersja: „4.5.4.1deb2ubuntu2”, serwer bazy danych: „10.2.14-MariaDB-10.2.14 + maria ~ xenial - dystrybucja binarna mariadb.org”.

Mam biegać SELECTpoleceń używając JOINi USINGzarówno phpMyAdmin aw terminalu (wiersza poleceń), a te w phpMyAdmin produkować pewne reakcje zaskakujący:

1) LIMITklauzula na końcu wydaje się być ignorowana.
2) przypuszczalna liczba wierszy podana na górze strony z wynikami jest czasami niepoprawna: na przykład zwracane są 4, ale na górze jest napisane: „Wyświetlanie wierszy 0–24 (łącznie 2503, zapytanie zajęło 0,0018 sekundy). „

Normalne logowanie do mysql i uruchamianie tych samych zapytań nie powoduje tych błędów. Te błędy również nie występują podczas uruchamiania tego samego zapytania w phpMyAdmin przy użyciu JOIN ... ON .... Prawdopodobnie błąd phpMyAdmin.