PostgreSQL FULL JOIN
TO‘LIQ QO‘SHILING
FULL JOIN kalit so‘zi mos kelmasa ham, ikkala jadvaldagi BARCHA yozuvlarni tanlaydi. Mos keladigan satrlar uchun ikkala jadvaldagi qiymatlar mavjud, agar mos kelmasa, bo‘sh maydonlar NULL qiymatini oladi.
Keling, soxta sinov mahsulotlari jadvalimizdan foydalangan holda misolni ko‘rib chiqaylik:
testproduct_id | product_name
| category_id
----------------+------------------------+-------------
1 | Johns Fruit Cake |
3
2 | Marys Healthy Mix |
9
3 | Peters Scary Stuff |
10
4 | Jims Secret Recipe |
11
5 | Elisabeths Best Apples |
12
6 | Janes Favorite Cheese |
4
7 | Billys Home Made Pizza |
13
8 | Ellas Special Salmon |
8
9 | Roberts Rich Spaghetti |
5
10 | Mias Popular Ice |
14
(10 rows)
Biz toifalar jadvali bilan sinov mahsulotlari jadvaliga qo‘shilishga harakat qilamiz:
category_id | category_name |
description
-------------+----------------+------------------------------------------------------------
1 | Beverages | Soft drinks, coffees, teas,
beers, and ales
2 | Condiments | Sweet and savory sauces, relishes,
spreads, and seasonings
3 | Confections | Desserts, candies, and sweet breads
4 | Dairy Products | Cheeses
5 | Grains/Cereals | Breads, crackers, pasta, and cereal
6 | Meat/Poultry | Prepared meats
7 | Produce | Dried fruit and bean
curd
8 |
Seafood | Seaweed and fish
(8
rows)
Eslatma: Sinov mahsulotlaridagi ko‘pgina mahsulotlarda kategoriyalar jadvalidagi toifalarning hech biriga mos kelmaydigan kategoriya_id mavjud.
FULL JOIN-dan foydalanib, biz toifalar jadvali va sinov mahsulotlari jadvalidagi barcha yozuvlarni olamiz:
Misol
Kategoriya_id ustunidan foydalanib, test mahsulotlarini toifalarga qo‘shing:
SELECT
testproduct_id, product_name, category_name
FROM testproducts
FULL JOIN categories ON testproducts.category_id = categories.category_id;
Misolni ishga tushirish »
Natija
Ikkala jadvaldagi barcha yozuvlar qaytariladi.
Mos bo‘lmagan qatorlar qarama-qarshi jadvaldagi maydonlarda NULL qiymatini oladi:
testproduct_id | product_name
| category_name
----------------+-------------------------+----------------
1 | Johns Fruit Cake | Confections
2 | Marys Healthy Mix |
3 | Peters Scary Stuff |
4 | Jims Secret Recipe |
5 | Elisabeths Best Apples |
6 | Janes Favorite Cheese | Dairy Products
7 | Billys Home Made Pizza |
8 | Ellas Special Salmon | Seafood
9 | Roberts Rich Spaghetti | Grains/Cereals
10 | Mias Popular Ice |
|
| Condiments
|
| Meat/Poultry
|
| Beverages
|
| Produce
(14 rows)
Eslatma: FULL JOIN va FULL OUTER JOIN bir xil natija beradi.
OUTER - FULL JOIN uchun standart birlashma turi, shuning uchun siz FULL JOIN deb yozganingizda tahlilchi aslida FULL OUTER JOIN deb yozadi.
W3Schools Pathfinder
Yutuqlaringizni kuzating – bu bepul!