14-amaliy mashg‘ulot Ko’pjadvalli amallardan foydalanish. Ishdan maqsad

AuthorID AuthorName 1 Bruce Eckel 2 Robert Lafore 3 Andrew Tanenbaum

Books BookID BookName 3 Modern Operating System 1 Thinking in Java 3 Computer Architecture 4 Programming in Scala

JOIN – chap va o`ng tomondagi jadvallar birlashtiriladi va mos qo`yilgan atributlar orasidagi shart bajarilganda javob (yozuv) qaytariladi
chap_jadval INNER JOIN o`ng_jadval ON bog`lanish_sharti

INNER JOIN sintaksisi:
SELECT maydon nomlari [,... n]
FROM Jadval_1
INNER JOIN Jadval _2 ON bog`liqlik_sharti
Misol:
SELECT * FROM Authors A
INNER JOIN Books B ON A.AuthorID = B.BookID
Natija

A.AuthorID	A.AuthorName	B.BookID	B.BookName
3	Andrew Tanenbaum	3	Modern Operating System
1	Bruce Eckel	1	Thinking in Java
3	Andrew Tanenbaum	3	Computer Architecture

LEFT JOIN – chap tomon jadvalining barcha qatori qaytariladi. Bu qaytarilayotgan qiymatlarga mos o`ng tomon jadvalidan mos qiymatlar olinadi, agar mos qiymati bo`lmasa bo`sh qiymat (NULL) qaytariladi
chap_jadval LEFT OUTER JOIN o`ng_jadval ON bog`lanish_sharti

LEFT JOIN sintaksisi:
SELECT maydon nomlari [,... n] FROM Jadval_1
LEFT OUTER JOIN Jadval _2 ON bog`liqlik_sharti
Misol:
SELECT * FROM Authors A
LEFT OUTER JOIN Books B ON A.AuthorID = B.BookID
Natija

A.AuthorID	A.AuthorName	B.BookID	B.BookName
1	Bruce Eckel	1	Thinking in Java
2	Robert Lafore	NULL	NULL
3	Andrew Tanenbaum	3	Modern Operating System
3	Andrew Tanenbaum	3	Computer Architecture

LEFT JOIN orqali chap tomon jadvalining o`ng tomon jadvaliga tegishli bo`lmagan qatorlaridan holi qatorlarini ajratib olish mumkin. Ya`ni chap tomon jadvalidan o`ng tomon jadval elementlarini ayirib chap tomon jadval qatorlarini chiqarish mumkin.
chap_jadval LEFT OUTER JOIN o`ng_jadval ON bog`lanish_sharti WHERE o`ng_jadval IS NULL

Misol:
SELECT * FROM Authors A
LEFT OUTER JOIN Books B ON A.AuthorID = B.BookID
WHERE B.BookID IS NULL
RIGHT JOIN – o`ng tomon jadvalining barcha qatori qaytariladi. Bu qaytarilayotgan qiymatlarga mos chap tomon jadvalidan mos qiymatlar olinadi, agar mos qiymati bo`lmasa bo`sh qiymat (NULL) qaytariladi
chap_jadval RIGHT OUTER JOIN o`ng_jadval ON bog`lanish_sharti

RIGHT JOIN sintaksisi:
SELECT maydon nomlari [,... n] FROM Jadval_1
RIGHT OUTER JOIN Jadval _2 ON bog`liqlik_sharti
Misol:
SELECT * FROM Authors A
RIGHT OUTER JOIN Books B ON A.AuthorID = B.BookID
Natija

A.AuthorID	A.AuthorName	B.BookID	B.BookName
3	Andrew Tanenbaum	3	Modern Operating System
1	Bruce Eckel	1	Thinking in Java
3	Andrew Tanenbaum	3	Computer Architecture
NULL	NULL	4	Programming in Scala

RIGHT JOIN orqali o`ng tomon jadvalining chap tomon jadvaliga tegishli bo`lmagan qatorlaridan holi qatorlarini ajratib olish mumkin. Ya`ni o`ng tomon jadvalidan chap tomon jadval elementlarini ayirib o`ng tomon jadval qatorlarini chiqarish mumkin.
chap_jadval RIGHT OUTER JOIN o`ng_jadval ON bog`lanish_sharti WHERE o`ng_jadval IS NULL

Misol:
SELECT * FROM Authors A
RIGHT OUTER JOIN Books B ON A.AuthorID = B.BookID
WHERE A.AuthorID IS NULL


FULL JOIN – chap va o`ng tomon jadvallarining barcha qatori qaytariladi. Agar bog`lanish sharti chap va o`ng tomon jadvallarni qanoatlantirsa, ular bir qatorga birlashtiriladi. Bog`lanish sharti qanoatlantirilmasa, NULL qiymati bog`liq bo`lmagan qatorlarga ko`ra chap yoki o`ng tomon jadvallari o`rniga qo`yiladi
chap_jadval FULL OUTER JOIN o`ng_jadval ON bog`lanish_sharti

FULL JOIN sintaksisi:
SELECT maydon nomlari [,... n] FROM Jadval_1
FULL OUTER JOIN Jadval _2 ON bog`liqlik_sharti
Misol:
SELECT * FROM Authors A
FULL OUTER JOIN Books B ON A.AuthorID = B.BookID
Natija

A.AuthorID	A.AuthorName	B.BookID	B.BookName
1	Bruce Eckel	1	Thinking in Java
2	Robert Lafore	NULL	NULL
3	Andrew Tanenbaum	3	Modern Operating System
3	Andrew Tanenbaum	3	Computer Architecture
NULL	NULL	4	Programming in Scala

FULL JOIN orqali chap va o`ng tomondagi jadvallar birlashtirilishi va ularda mos qo`yilgan atributlar orasidagi shart bajarilgandan tashqari (teskari) yozuvlarini chiqarish mumkin.
chap_jadval FULL OUTER JOIN o`ng_jadval ON bog`lanish_sharti WHERE o`ng_jadval IS NULL OR chap_jadval IS NULL

Misol:
SELECT * FROM Authors A
FULL OUTER JOIN Books B ON A.AuthorID = B.BookID
WHERE A.AuthorID IS NULL OR B.BookID IS NULL
NATURAL JOIN – chap tomon jadvali bilan o`ng tomon jadvalining mos atributlari avtomatik aniqlanib, bu atributlar orasida shart bajarilsa javob qaytariladi. Xuddi INNER JOIN kabi
chap_jadval NATURAL JOIN o`ng_jadval
CROSS JOIN – chap tomon jadvalining har bir yozuvi o`ng tomon jadvalining har bir yozuviga mos quyiladi, ya`ni dekart ko`paytma bo`ladi