آموزش جامع SQL Server (جلسه هفت: اتصال جداول-بخش۲)
1398/02/21 17:46 , میلاد صاحب نظر

آموزش جامع SQL Server (جلسه هفت: اتصال جداول-بخش۲)

در جلسه قبل در مورد اتصال جداول به یکدیگر، انواع دستورهای query نویسی مرتبط با جداول به هم مرتبط و کاربردهای آن‌ها صحبت شد.

همچنین کل دستورات join معرفی شده و syntax آن‌ها ارائه شد و به صورت مفصل دستورات INNER JOIN و LEFT JOIN مورد بررسی قرار گرفتند.

در این جلسه دستورات CROSS JOIN، FULL OUTER JOIN، RIGHT JOIN و روش Self Join بررسی خواهند شد.

دستور Right Join در SQL Server

دستور Right Join داده‌های دو یا تعداد بیشتری جدول را با هم ترکیب می‌کند. این دستور کارش را با انتخاب داده از جدول سمت راست آغاز کرده و داده‌ها را با ردیف‌های جدول سمت چپ تطابق می‌دهد.

دستور Right Join مجموعه‌ای از نتایج را بازمی‌گرداند که شامل تمام ردیف‌های جدول سمت راست است، مهم نیست که ردیف‌های مشابهی از آن‌ها در جدل سمت چپ وجود داشته باشد یا خیر. اگر یک ردیف در جدول سمت راست هیچ ردیف مشابهی با جدول سمت چپ نداشته باشد، ستون متعلق به جدول سمت راست در مجموعه نتایج null خواهد بود.

کد زیر syntax دستور Right Join را نمایش می‌دهد:

SELECT 
    select_list
FROM 
    T1
RIGHT JOIN T2 ON join_predicate;

در این syntax، کد T1 جدول سمت چپ و T2 جدول سمت راست است.

توجه کنید که دستورهای RIGHT JOIN و RIGHT OUTER JOIN مشابه هم هستند. کلیدواژه OUTER اختیاری است. نمودار دایره‌ای زیر عملکرد RIGHT JOIN را نمایش می‌دهد:

عملکرد RIGHT JOIN

مثال RIGHT JOIN در SQL Server

برای مثال‌ها از جداول sales.order_items و production.products از پایگاه داده نمونه Bikestores استفاده می‌کنیم.

جداول sales.order_items و production.products

کد زیر تمام order_idهای جدول sales.order_items و نام محصول از جدول production.products را بازمی‌گرداند:

SELECT
    product_name,
    order_id
FROM
    sales.order_items o
    RIGHT JOIN production.products p 
        ON o.product_id = p.product_id
ORDER BY
    order_id;

نتایج به این شرح هستند:

نتایج

Query بالا تمام ردیف‌ها از جدول production.products (جدول سمت راست) و ردیف‌های جدول Sales.order_items (جدول سمت چپ) را بازگرداند. اگر یک محصول هیچ فروشی نداشته باشد، ستون order_id آن null خواهد بود.

برای به دست آوردن محصولاتی که هیچ فروشی ندارند، باید یک دستور WHERE به بالای query اضافه کنید تا محصولاتی که فروش دارند را فیلتر کند:

SELECT
    product_name,
    order_id
FROM
    sales.order_items o
    RIGHT JOIN production.products p 
        ON o.product_id = p.product_id
WHERE 
    order_id IS NULL
ORDER BY
    product_name;

تصویر زیر خروجی را نمایش می‌دهد:

نتایج

نمودار دایره‌ای زیر عملیات RIGHT JOIN کد بالا را نمایش می‌دهد.

نمودار

بسیار خب، این هم از کاربردهای دستور RIGHT JOIN به صورت کامل و شامل.

دستور FULL OUTER JOIN در SQL Server

این دستور مجموعه نتایجی را بازمی‌گرداند که شامل ردیف‌هایی از هر دو جداول سمت راست و چپ است. وقتی هیچ ردیف مشترکی برای ردیف مورد نظر در جدول سمت چپ وجود نداشته باشد، ستون‌های جدول سمت راست null خواهند شد.

به طور مشابه، وقتی هیچ ردیف مشابهی برای ردیف مورد نظر در جدول سمت راست وجود نداشته باشد، ستون جدول سمت چپ null خواهد شد. کد زیر syntax دستور FULL OUTER JOIN را در هنگام اتصال دو جدول T1 و T2 نمایش می‌دهد:

SELECT 
    select_list
FROM 
    T1
FULL OUTER JOIN T2 ON join_predicate;

کلیدواژه OUTER اختیاری است، بنابراین می‌توانید آن را درج نکنید. مانند مثال زیر:

SELECT 
    select_list
FROM 
    T1
FULL JOIN T2 ON join_predicate;

در این syntax:

  • ابتدا، جدول سمت چپ یعنی T1 در دستور FROM مشخص شده است.
  • سپس، جدول سمت راست یعنی T2 و یک شرط اتصال مشخص شده‌اند.

نمودار دایره‌ای زیر دو مجموعه نتایج حاصل از دستور FULL OUTER JOIN را نمایش می‌دهد.

نمودار

مثال دستور full outer join در SQL Server

اجازه دهید چند جدول نمونه ایجاد کنم و عملکرد دستور FULl OUTER JOIN را نمایش دهیم.

ابتدا، یک شمای جدید به نام pm ایجاد می‌کنیم که مخفف مدیران پروژه (project managements) است.

CREATE SCHEMA pm;
GO

سپس جداول جدیدی به نام projects و members در شمای pm ایجاد می‌کنیم:

CREATE TABLE pm.projects(
    id INT PRIMARY KEY IDENTITY,
    title VARCHAR(255) NOT NULL
);
 
CREATE TABLE pm.members(
    id INT PRIMARY KEY IDENTITY,
    name VARCHAR(120) NOT NULL,
    project_id INT,
    FOREIGN KEY (project_id) 
        REFERENCES pm.projects(id)
);

فرض کنید هر عضو فقط می‌توانید در یک پروژه شرکت کند و هر پروژه دارای صفر یا تعداد بیشتری عضو است. اگر یک پروژه در فاز ایده باشد، پس هیچ عضوی ندارد.

سپس، چند ردیف در جداول projects و members ایجاد می‌کنیم:

INSERT INTO 
    pm.projects(title)
VALUES
    ('New CRM for Project Sales'),
    ('ERP Implementation'),
    ('Develop Mobile Sales Platform');
 
 
INSERT INTO
    pm.members(name, project_id)
VALUES
    ('John Doe', 1),
    ('Lily Bush', 1),
    ('Jane Doe', 2),
    ('Jack Daniel', null);

بعد از آن، از این دو جدول داده درخواست می‌کنیم:

SELECT * FROM pm.projects;

جدول products

SELECT * FROM pm.members;

جدول members

در آخر، از دستور FULL OUTER JOIN برای استخراج داده از این دو جدول استفاده می‌کنیم:

SELECT 
    m.name member, 
    p.title project
FROM 
    pm.members m
    FULL OUTER JOIN pm.projects p 
        ON p.id = m.project_id;

خروجی به شرح زیر است:

نتایج

در این مثال، query اعضایی را که در پروژه‌ها شرکت کردند، اعضایی که در هیچ پروژه‌ای شرکت نکردند و پروژه‌هایی که هیچ عضوی ندارند را بازگرداند.

برای یافتن اعضایی که در هیچ پروژه‌ای شرکت نکردند و پروژه‌هایی که هیچ عضوی ندارند، باید یک دستور WHERE به بالای query اضافه کنید:

SELECT 
    m.name member, 
    p.title project
FROM 
    pm.members m
    FULL OUTER JOIN pm.projects p 
        ON p.id = m.project_id
WHERE
    m.id IS NULL OR
    P.id IS NULL;

تصویر زیر خروجی را نمایش می‌دهد:

نتایج

همان‌طور که به وضوح در خروجی مشخص است، Jack Daniel در هیچ پروژه‌ای شرکت نکرده است و Develop Mobile Sales Platform هیچ عضوی ندارد.

به این شکل به صورت کامل با کاربردهای دستور FULL OUTER JOIN نیز آشنا شدید.

دستور Cross Join در SQL Server

دستور CROSS JOIN دو یا تعداد بیشتری جداول نامربوط را به هم متصل می‌کند.

کد زیر yntax دستور CROSS JOIN را برای دو جدول نمایش می‌دهد:

SELECT
 select_list
FROM
 T1
CROSS JOIN T2;

CROSS JOIN همه ردیف‌ها از اولین جدول (T1) را با همه ردیف‌ها از جدول دوم (T2) متصل کرد. به عبارت دیگر، CROSS JOIN یک حاصل دکارتی از ردیف‌های هر دو جدول را بازمی‌گرداند.

بر خلاف دستورهای INNER JOIN یا LEFT JOIN، دستور CROSS JOIN یک ارتباط بین جداول به هم متصل ایجاد نمی‌کند.

فرض کنید جدول T1 حاوی ردیف‌های 2، 1 و 3 بوده و جدول T2 حاوی سه ردیف B، A و C است.

دستور CROSS JOIN یک ردیف از جدول اول (T1) می‌گیرد و سپس یک ردیف جدید به ازای هر ردیف در جدول دوم (T2) ایجاد می‌کند. سپس همین کار را برای ردیف بعدی از جدول اول (T1) انجام می‌دهد و الی آخر.

عملکرد CROSS JOIN

در این تصویر، CROSS JOIN در کل ۹ ردیف ایجاد کرده است. در کل، اگر جدول اول دارای n ردیف و جدول دوم دارای m ردیف باشد، آنگاه cross join در مجموعه تعداد m×n ردیف ایجاد خواهد کرد.

مثال‌های CROSS JOIN در SQL Server

کد زیر ترکیبات تمام محصولات و فروشگاه‌ها را بازمی‌گرداند. مجموعه نتایج می‌توانند برای رویه ارزیابی دوره‌های ماهانه و سالانه استفاده کرد:

SELECT
    product_id,
    product_name,
    store_id,
    0 AS quantity
FROM
    production.products
CROSS JOIN sales.stores
ORDER BY
    product_name,
    store_id;

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

نتایج

کد زیر محصولاتی را می‌یابد که هیچ فروشی در هیچ فروشگاهی ندارند:

SELECT
    s.store_id,
    p.product_id,
    ISNULL(sales, 0) sales
FROM
    sales.stores s
CROSS JOIN production.products p
LEFT JOIN (
    SELECT
        s.store_id,
        p.product_id,
        SUM (quantity * i.list_price) sales
    FROM
        sales.orders o
    INNER JOIN sales.order_items i ON i.order_id = o.order_id
    INNER JOIN sales.stores s ON s.store_id = o.store_id
    INNER JOIN production.products p ON p.product_id = i.product_id
    GROUP BY
        s.store_id,
        p.product_id
) c ON c.store_id = s.store_id
AND c.product_id = p.product_id
WHERE
    sales IS NULL
ORDER BY
    product_id,
    store_id;

تصویر زیر بخشی از نتایج را نمایش می‌دهد:

نتایج

بدین ترتیب، چگونگی استفاده از دستور CROSS JOIN در SQL Server برای ایجاد نتایج دکارتی از ردیف‌های جداول متصل را آموختید.

روش Self Join در SQL Server

روش Self Join به شما امکان می‌دهد تا یک جدول را به خودش متصل کنید. این امر برای دریافت داده‌های سلسله‌مراتبی یا مقایسه ردیف‌ها درون یک جدول مفید است.

این روش از دستورهای inner join یا left join استفاده می‌کند. چون query که از روش Self join استفاده می‌کند، به یک جدول اشاره می‌کند، پس از alias جدول برای اختصاص دادن نام‌های مختلف به جدول درون query استفاده شده است.

توجه کنید که اگر در یک query بدون استفاده از aliasهای جدول، بیش از یک بار به یک جدول مشابه اشاره کنید، برنامه به شما ارور خواهد داد.

کد زیر syntax اتصال جدول T به خودش را نمایش می‌دهد:

SELECT
    select_list
FROM
    T t1
[INNER | LEFT]  JOIN T t2 ON
    join_predicate; 

Query بالا دو بار به جدول T اشاره می‌کند. برای اختصاص دادن نام‌های مختلف به جدول T از aliasهای t1 و t2 استفاده شده است.

مثال‌های روش self join در SQL Server

اجازه دهید چند مثال بزنیم تا به خوبی نحوه عملکرد روش Self join را درک کنید.

1) استفاده از self join برای دریافت داده‌های سلسله مراتبی

جدول staffs از پایگاه داده نمونه Bikestores را در نظر بگیرید:

جدول staffs

جدول staffs

جدول Staffs اطلاعات کارکنان (مانند id، نام، نام خانوادگی و ایمیل) را ذخیره می‌کند. همچنین دارای یک ستون به نام manager_id است که مدیر مستقیم را مشخص می‌کند. برای مثال، Mireya برای Fabiola کار می‌کند، چون Fabiola هیچ مدیری ندارد. بنابراین ستون manager_id دارای یک NULL است.

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

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
INNER JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

نتایج

در این مثال، دو بار به جدول staffs اشاره کردیم: یک بار به شکل e برای کارمندان و بار دیگر به شکل m برای مدیران. شرط اتصال با استفاده از مقادیر موجود در ستون‌های manager_id و m.staff_id تطابق داده می‌شد.

ستون employee دارای Fabiola Jackson نیست و این به خاطر تأثیر INNER JOIN است. اگر دستور INNER JOIN را با دستور LEFT JOIN جایگزین کنید (همان‌طور که در query زیر مشاهده می‌کنید)، مجموعه نتایجی را به دست می‌آورید که شامل Fabiola Jackson در ستون employee است:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

نتایج

2) استفاده از روش self join برای مقایسه ردیف‌ها درون یک جدول

جدول customers را مشاهده کنید:

جدول customers

کد زیر از روش self join برای یافتن مشتریان واقع در یک شهر استفاده می‌کند:

SELECT
    c1.first_name + ' ' + c1.last_name customer_1,
    c2.first_name + ' ' + c2.last_name customer_2,
    c1.city
FROM
    sales.customers c1
INNER JOIN sales.customers c2 ON c1.customer_id <> c2.customer_id
AND c1.city = c2.city
ORDER BY
    customer_1,
    customer_2;

نتایج

به این شکل به طور کامل با روش Self Join و کاربردهای آن نیز آشنا شدید.

خلاصه

در این دو جلسه اخیر، به صورت کامل مبحث اتصال جداول را بررسی کردیم، دستورات آن را معرفی، تعریف و بررسی کردیم و به صورت پروژه محور برای آن‌ها مثال حل کردیم و با آن‌ها آشنا شدید.

در جلسه بعدی در مورد گروه‌بندی داده‌ها صحبت خواهیم کرد.

با MUG همراه باشید!

منبع: sqlservertutorial

 مطالب مرتبط

۱۳ دستور مهم SQL که هر برنامه‌نویس باید بداند
چگونه از حملات نفوذ کننده به SQL جلوگیری کنیم؟
 طبقه بندی زبان های برنامه نویسی
روش‌های هوشمندانه برای استفاده از "تلفیق" در SQL
 LINQ(زبان جستجوی یکپارچه)
برنامه نویسی شیءگرا چیست؟

از آخرین دوره های آموزشی و تخفیف ها مطلع شوید

با تکمیل فرم زیر ، از اخبار و اطلاعات به روز برنامه نویسی و تکنولوژی عقب نمانید

آخرین مطالب

۵ قالب رایگان HTML برای ایجاد آسان و سریع وبسایت‌ها!
۵ قالب رایگان HTML برای ایجاد آسان و سریع وبسایت‌ها!

چه مالک یک کسب و کار باشید، چه صرفاً بخواهید برند شخصی خودتان و مهارت‌هایتان را تبلیغ کنید، ...

آموزش جامع SQL Server (جلسه نه: Subquery)
آموزش جامع SQL Server (جلسه نه: Subquery)

در این جلسه در مورد Subquery صحبت می‌کنیم. منظور از Subquery یک query است که درون یک عبارت ...

آموزش مقدماتیHTMLوCSS(جلسه ده:تعیین موقعیت محتوا – بخش۲)
آموزش مقدماتیHTMLوCSS(جلسه ده:تعیین موقعیت محتوا – بخش۲)

در جلسه قبل وارد مبحث تعیین موقعیت محتوا شدیم و توضیح دادیم که می‌توانیم این کار را با ...

آخرین دیدگاه ها

دیدگاه خود را درباره این پست بنویسید

فرم ارسال نظرات