Website Development Prices

Website Development Prices

Monday, October 19, 2015

Korelirani podupiti (Correlated subqueries)

Korelirani podupiti su nesto slozeniji jer elemente spoljnog upita mozete upotrebiti u unutrasnjem upitu. Traze se redovi iz unutrasnjeg upita koji odgovaraju redovima iz spoljasnjeg upita.

Primer:

select brojmotora, imemotora
from motori
where not exists
(select * from stvari_porudzbina 
where stvari_porudzbina.brojmotora=motori.brojmotora);

Ova fajl sacuvajte kao korelirani-podupiti.sql.

Napomena: posto je do sada kupljen svaki motor, za potrebe ovog primera, dodala sam dva nova motora. Da bi ste uneli ove podatke sledite korake 1, 2, 3.


insert into motori values
  ("985113002", "Yamaha", "YZF-R1M", 9758.00),

  ("672113002", "Kawasaki", "Ninja H2", 8900.00);

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 ovaj kod i kliknite dugme "Go".




Objasnjenje:

Ovaj upit ilustruje  upotrebu koreliranih podupita i upotrebu  poslednjeg specijalnog operatora za podupite, exists. Ovaj upit ucitava sve motore koje jos niko nije kupio. Tabela stvari_porudzbina pominje se na listi u odredbi from, ali se vrednosti iz nje porede s kolonom motori.brojmotora. Drugacije receno, unutrasnji upit referencira podatke iz spoljasnjeg upita.

Operator exists vraca true ako u podupitu postoji barem jedan red koji odgovara tekucem redu iz spoljasnjeg upita. Operator not exists vraca true ako  u podupitu ne postoji nijedan red koji odgovara tekucem redu iz spoljasnjeg upita.

Correlated subqueries are somewhat more complex because the elements of outer query can be used in the inner query. They're looking for the rows from the inner query corresponding to rows from the outer query.

Example:

SELECT motobikeid, motobike_name
FROM motobikes
WHERE NOT EXISTS
(SELECT * FROM orders_items 
WHERE orders_items.motobikeid=motobikes.motobikeid);

Save this file as correlated-subqueries.sql.

Note: since, so far, each motobike is bought, for purposes of this example, I added two new motobikes. To enter this data in db, follow the steps 1, 2, 3.

INSERT INTO motobikes VALUES
  ("985113002", "Yamaha", "YZF-R1M", 9758.00),

  ("672113002", "Kawasaki", "Ninja H2", 8900.00);

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 lovebikes, and on the right select the SQL tab.


3. Insert this code and click "Go".




Explanation:

This query illustrates the use of correlated subqueries and use of the last special operator for subqueries, EXISTS. This query loads all the motobikes that still no one bought. Table orders_items mentioned is on the list of the clause FROM, but the values of it are compared to the column motobikes.motobikeid. In other words, the inner query references the data from the outer query.


Operator EXISTS returns true if in the subquery is at least one row that matches the current row from outer query. Operator NOT EXISTS returns true if in the subquery is no row that corresponds to the current row of outer query.

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.