Pronalazenje redova koji se ne poklapaju (Finding rows that do not match)

Drugi osnovni tip koji se koristi u MySQL-u jesu levi spojevi (eng. left join). 

U prethodnim primerima bili su ucitani samo redovi u kojima je postojalo poklapanje izmedju odataka.  Ako zelimo redove u kojima to nije slucaj, npr. klijente koji nikad nista nisu kupili ili motore koje jos niko nije kupio.

Resenje je da upotrebite levi spoj. Takav spoj uparuje redove dveju tabela na osnovu odredjenog uslova. Ako u tabeli na desnoj strani ne postoji red koji bi odgovarao redu tabele na levoj strani , skupu rezultata se dodaje red koji, umesto vrednosti iz kolone desne tabele, sadrzi vrednost NULL. 

Primer 1 (ovaj kod sacuvajte kao pronalazenje-redova.sql):

select kupci.sifrakupca, kupci.ime, porudzbine.brojporudzbine
from kupci left join porudzbine

on kupci.sifrakupca = porudzbine.sifrakupca;

Objasnjenje:

Levim spojem povezane su tabele kupci i porudzbine. Sintaksa uslova spajanja u ovom tipu spoja nesto drugacija - uslov spajanja sadrzi i odredbu on.


1. Pokrenite XAMPP. Kliknite na Admin u liniji MySQL-a ili ukucajte u address bar http://localhost/phpmyadmin/

2. Sa leve strane izaberite bazu podataka volimmotore, a sa desne izberite karticu SQL.
3. Ubacite sledeci kod i kliknite dugme "Go".

Primer 2:

Sledeci kod dodajte u pronalazenje-redova.sql.

insert into kupci values
  (NULL, "Ivan Ivanic", "Ivanova 41", "Pula"),

  (NULL, "Iva Ivic", "Nikoliceva 22", "Zadar");

Objasnjenje:

Dodali smo ovaj kod zato sto do sada svi nasi kupci su kupili motore a za ovaj primer nam trebaju kupci koji nisu kupili nista.

4. Ubacite prvi kod iz fajla pronalazenje-redova.sql, na karticu SQL i kliknite dugme "Go" (sa leve strane treba da bude izabrana baza podataka volimmotore, ako vec prethodno nije bila izabrana).

Objasnjenje:

Dobijeni rezultat pokazuje da u tabeli porudzbine ne postoje redovi koji bi odgovarali identifikatoru  brojporudzbine kupaca Ivana Ivanica i Ive Ivic jer za njih dvoje kolona brojporudzbine sadrzi vrednost NULL.

Ako zelite da vidite klijente koji jos nista nisu kupili, potrazite one kojima umesto vrednosti iz polja primarnog kljuca tabele na desnoj strani (u ovom slucaju brojporudzbine), odgovara samo vrednost NULL.

Sledeci kod dodajte u pronalazenje-redova.sql.

select kupci.sifrakupca, kupci.ime
from kupci left join porudzbine
using (sifrakupca)

where porudzbine.brojporudzbine is null;

Objasnjenje:

Levi spojevi podrzavaju podrzavaju sintaksu on iz prvog primera ili sintaksu using iz drugog primera. Posto u varijanti sa using ne zadajte iz koje tabele potice zajednicki atribut, ako zelite da koristite tu sintaksu, spojna polja moraju imati isto ime u obe tabele.

Resenje za ovu vrstu pitanja mozete resiti i upotrebom podupita.

The second basic type used in MySQL is left join.

In the above examples were loaded only rows where there was overlap between the data. If you want the rows where this is not the case, for example, clients that never bought motobikes or motobikes that still no one bought.

The solution is to use the left join. Such a connection is pairing two rows of tables based on certain conditions. If in the table on the right side there is no a row that can fit the row of the table on the left, to the result set is added row that, instead of the values from the right column, contains value NULL.

Example 1 (tsave this code as finding-rows.sql):

SELECT customers.customerid, customers.name, orders.ordersid
FROM customers LEFT JOIN orders

ON customers.customerid = orders.customerid;

Explanation:

With the left join tables customers and orders are linked. The syntax of the joining in this type of configuration is somewhat different - a condition of the join include clause ON.

1. Start XAMPP. Click on the Admin in line MySQL or type in the address bar 
http://localhost/phpmyadmin/

2. From the left select the database lovemotobikes, and on the right select the SQL tab.
3. Insert the following code and click "Go".

Example 2:


Add next code to finding-rows.sql.

INSERT INTO customers VALUES
  (NULL, "Ivan Ivanic", "Ivanova 41", "Pula"),

  (NULL, "Iva Ivic", "Ivina 75", "Zadar");

Explanation:

We've added this code because so far all of our customers have purchased the motobikes and for this example we need customers who have not bought anything.

4. Insert the first code from a finding-rows.sql, on the SQL tab and click the "Go" (on the left should be selected database lovemotobikes, if already not been previously selected).

Explanation:

The obtained result shows that in table orders there are no rows to match the identifier ordersid for customers Ivana Ivanic and Iva Ivic because for this two, column ordersid contains value NULL.

If you like to see clientss who have not bought anything yet, look for those which, instead of the primary key fields of the table on the right side (in this case ordersid), corresponding to only value NULL.


Add next code to finding-rows.sql.

SELECT customers.customerid, customers.name
FROM customers LEFT JOIN ordersid
USING (customerid)

WHERE orders.ordersid IS NULL;

Note: in future instead of database lovemotobikes, I'll be using database lovebikes. I had some errors and I resolved it by creating new database.

Explanation:

Left join supports syntax ON in the first example or syntax USING in the second example. Since in the version with USING you don't specify from which table is coming common attribute, if you want to use this syntax, joining fields must have the same name in both tables.


The solution for this type of questions can be solved by using the subquery.