Raqamli texnologiyalarni rivojlantirish vazirligi muhammad al-xorazmiy nomidagi toshkent
- Bu səhifədəki naviqasiya:
- A.AuthorID A.AuthorName B.BookID
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
chap_jadval CROSS JOIN o`ng_jadval
CROSS JOIN sintaksisi:
SELECT maydon nomlari [,... n] FROM Jadval_1
CROSS JOIN Jadval _2 ON bog`liqlik_sharti
Misol:
SELECT *
FROM Authors A
CROSS JOIN Books B
Natija
|
Yüklə 66,01 Kb. Dostları ilə paylaş: 
|