آموزش postgres - دستور join

در این آموزش قصد داریم به بررسی inner join, left join, right join و full join در postgres بپردازیم.

به طور ساده و خلاصه، زمانی از از دستور join استفاده می‌کنیم که می‌خواهیم داده‌های‌ِ ستون‌های یک جدول (self join) یا چند جدول رو با هم ترکیب کنیم و یک ساختار جدید بدست آوریم که عموما در این ترکیب‌ِ ستون های مشترک، یک ستون در یک جدول نقش ستون نگهدارنده کلید اصلی و در جدول دیگر نقشِ ستونِ کلید خارجی را دارد.

در postgres انواع joinها به شکل زیر است:

• inner join
• left join
• right join
• full outer join
• cross join
• natural join
• self join

خب، اجازه بدید که با مثال پیش بریم، به دو جدول به اسم‌های basket_a و basket_b نیاز داریم، ابتدا آنها را ایجاد می‌کنیم و چند داده‌ در هر کدام از آنها ذخیره می‌کنیم:

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a) 
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b) 
VALUES
    (1, 'Orange'),
    (2, 'Apple'), 
    (3, 'Watermelon'),
    (4, 'Pear');

اگر از جدول های بالا لیست بگیریم خروجی مشابه با زیر خواهد بود:

بررسی inner join

قصد داریم که میوه‌های مشترک بین جدول basket_a و basket_b رو لیست کنیم، به این شکل عمل می‌کنیم:

SELECT
    a,    fruit_a,     b,    fruit_b 
FROM
    basket_a
 INNER JOIN basket_b
   ON fruit_a = fruit_b;

در دستور بالا، دستور inner join تمام مقادیر ستون fruit_a از جدول basket_a رو با مقادیر ستون fruit_b از جدول basket_b مقایسه میکنه و جاهایی که مقادیر ستون fruit_a با ستون fruit_b برابر باشد را برمیگرداند.

به خروجی دستور توجه کنید:

در واقع اگر بخواهیم رفتار inner join را روی نمودار venn نشان بدهیم به این شکل خواهد بود:

بررسی left join

زمانیکه که دو جدول رو با یکدیگر left join می‌کنیم به جدول نخست جدول left و به جدول دوم، جدول right می‌گوییم. دو جدولی که در ابتدای آموزش ساختیم را در نظر بگیرید. قصد داریم تا کوئری زیر را روی آنها اجرا کنیم:

SELECT
    a,    fruit_a,     b,     fruit_b
 FROM    basket_a 
LEFT JOIN basket_b
    ON fruit_a = fruit_b;

در left join ابتدا تمام داده‌های ستون fruit_a از جدول basket_a (چونکه جدول left هست) با داده‌های ستون fruit_b از جدول basket_b مقایسه می‌شوند. اگر بین آنها اشتراکی وجود داشته باشد، left join سطرهای جدیدی می‌سازد که شامل این داده ‌های مشترک است و اگر چیزی از مقادیر fruit_a با داده‌های ستون fruit_b مشترک نباشد، left join با ستون‌های ذکر شده در select سطر‌های جدیدی میسازد اما اینبار ستون‌های جدول basket_b مقدار null را دارند.

به خروجی توجه کنید:

اگر بخواهیم این نوع از join رو روی نمودار نشون بدیم به شکل زیر خواهد بود:

اگر شما نیاز داشته باشید که داده‌های را لیست کنید که در جدول left وجود داشته باشند اما در جدول right نباشند می‌توانید کوئری زیر رو اجرا کنید:

SELECT
    a,    fruit_a,    b,     fruit_b
FROM
    basket_a
LEFT JOIN basket_b
   ON fruit_a = fruit_b
 WHERE b IS NULL;

به خروجی این کوئری توجه کنید:

و نمودار venn برای این دستور به این شکل خواهد بود:

بررسی right join

این نوع از join برعکس left join عمل می‌کند. یعنی در واقع بررسی داده‌ها از جدول right شروع می‌شود و با یک ستون از جدول left مقایسه می‌شوند.

یعنی برای right join از جدول های بالا می‌توان گفت که، تمام مقادیر ستون fruit_b از جدول basket_b با مقادیر ستون fruit_a از جدول basket_a مقایسه می‌شوند و هر مقایسه در نتیجه اش یک سطر ایجاد می‌شود از ستون‌های تعیین شده‌ در دستور SELECT که اگر این مقایسه اشتراکی داشته باشد، مقادیر مشترک در سطر جای میگیرند و اگر مقادیری از ستون fruit_b باشد که هیچ اشتراکی با fruit_a نداشته باشد، برای مقادیر جدول left که همان basket_a است، در سطر جدید، مقدار null قرار می‌گیرد.

SELECT
    a,    fruit_a,    b,    fruit_b
 FROM    basket_a
 RIGHT JOIN basket_b
 ON fruit_a = fruit_b;

که خروجی دستور بالا به شکل زیر خواهد بود:

و نمایش دستور right join روی نمودار venn که به شکل زیر خواهد بود:

به طور مشابه با left join شما می‌توانید با اعمال یک شرط تعیین کنید که تنها داده‌هایی لیست شوند که در جدول right قرار دارند و در جدول left وجود ندارند.

SELECT
    a,    fruit_a,    b,    fruit_b 
FROM
    basket_a
 RIGHT JOIN basket_b
    ON fruit_a = fruit_b
 WHERE a IS NULL;

که خروجی آن به این شکل خواهد بود:

و نمایش این حالت از ‌right join روی نمودار venn به این شکل خواهد بود:

بررسی full join

در این نوع از join داده‌های مشترک از بین هر دو جدول (right - left ) دریافت می‌شوند و داده‌هایی که بین جدول‌ها مشترک نباشد برای آنها مقدار null درنظر گرفته می‌شود.

SELECT
    a,    fruit_a,    b,    fruit_b
 FROM    basket_a
 FULL OUTER JOIN basket_b
     ON fruit_a = fruit_b;

خروجی دستور بالا به شکل زیر خواهد بود:

و نمودار venn برای این join به شکل زیر خواهد بود:

گاهی اوقات ممکن است نیاز داشته باشید که داده‌هایی را از جدول‌های دریافت کنید که هیچ اشتراکی با یکدیگر نداشته باشند، برای این کار تنها کافیست که دستور زیر را اجرا کنید:

SELECT    a,    fruit_a,    b,    fruit_b
 FROM    basket_a
 FULL JOIN basket_b
    ON fruit_a = fruit_b
 WHERE a IS NULL OR b IS NULL;

خروجی این دستور به شکل زیر خواهد بود :

و نمودار venn برای این دستور:

در آموزش‌های بعدی، به انواع دیگر از join خواهیم پرداخت اما تا اینجای کار امیدوارم که این آموزش براتون مفید واقع شده باشه و در ضمن خوشحال میشم که نظرات شما رو در خصوص این آموزش بدونم.

پایان/