آموزش جامع SQL Server (جلسه شش: اتصال جداول – بخش ۱)
1398/02/18 14:28 , میلاد صاحب نظر

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

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

  • مبحث Joins: در این مبحث تمام انواع join در SQL Server را اختصار معرفی کرده و بعد به صورت مفصل با مثال مورد بررسی قرار می‌دهیم.

  • دستور INNER JOIN: ردیف‌های جدولی که دارای ردیف‌های یکسان در یک جدول دیگر است را انتخاب می‌کند.

  • دستور LEFT JOIN: همه ردیف‌ها را از جدول سمت چپ و ردیف‌های مشابه را از جدول سمت راست بازمی‌گرداند. در صورتی که جدول سمت راست هیچ ردیف مشابهی نداشته باشد، از مقادیر null به عنوان مقادیر ستون جدول سمت راست استفاده می‌کند.

  • دستور RIGHT JOIN: برعکس دستور LEFT JOIN عمل می‌کند.

  • دستور FULL OUTER JOIN: ردیف‌های مشابه را از هر دو جدول‌های چپ و راست بازمی‌گرداند. در صورتی که در هیچ کدام از جداول ردیف مشابهی وجود نداشته باشد، تمام ردیف‌های هر دو جدول را بازمی‌گرداند.

  • دستور CROSS JOIN: چند جدول نامربوط را به یکدیگر متصل می‌کند و نتایج دکارتی ردیف‌ها را در جداول به هم متصل ایجاد می‌کند.

  • روش Self Join: نشان می‌دهد که چگونه می‌توان از self-join به منظور نوشتن query برای داده‌های سلسله مراتبی استفاده کرد و ردیف‌های داخل یک جدول را با هم مقایسه می‌کند.

۱- نصب و آشنایی با SQL Server

۱۰-عملگرهای SET وCTEوPIVOT

۱۹-indexها بخش۱

۲۸-توابع تاریخ یا date بخش۲

۲-نوشتن query

۱۱-تغییر داده‌ها

۲۰-indexها بخش۲

۲۹-توابع رشته‌ای بخش۱

۳-محدود کردن ردیف‌ها

۱۲-تعریف داده‌ها بخش۱

۲۱-stored procedureها بخش۱

۳۰- توابع رشته‌ای بخش۲

۴-فیلتر کردن داده‌ها بخش ۱

۱۳-تعریف داده‌ها بخش۲

۲۲- stored procedureها بخش۲

۳۱- توابع رشته‌ای بخش۳

۵-فیلتر کردن داده‌ها بخش۲

۱۴-data typeهای بخش۱

۲۳- stored procedureها بخش۳

۳۲-توابع سیستمی بخش ۱

۶-اتصال جداول بخش۱

۱۵-data typeها بخش۲

۲۴-توابع تعریف شده توسط کاربر

۳۳-توابع سیستمی بخش۲

۷-اتصال جداول بخش۲

۱۶-ویژگی‌ها

۲۵-triggerها

۳۴-window functionها بخش۱

۸-گروه‌بندی داده‌ها

۱۷-expressionها

۲۶-توابع تجمعی

۳۵-window functionها بخش ۲

۹-subquery

۱۸-viewها

۲۷-توابع تاریخ یا date بخش۱

 

Joinها در SQL Server

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

SQL Server از انواع زیادی از joinها پشتیبانی می‌کند، از جمله INNER JOIN، LEFT JOIN، RIGHT JOIN، FULL OUTER JOIN و CROSS JOIN. هر نوع join مشخص می‌کند که SQL Server چگونه از داده‌های یک جدول برای انتخاب ردیف‌ها در یک جدول دیگر استفاده می‌کند.

اجازه دهید برای نمایش هر کدام از این دستورات، جداول نمونه ایجاد کنیم.

ایجاد جداول نمونه

ابتدا، یک شمای جدید به نام hr ایجاد می‌کنیم:

CREATE SCHEMA hr;
GO

دوم، دو جدول جدید به نام‌های candidates و employees در شمای hr ایجاد می‌کنیم:

CREATE TABLE hr.candidates(
    id INT PRIMARY KEY IDENTITY,
    fullname VARCHAR(100) NOT NULL
);
 
CREATE TABLE hr.employees(
    id INT PRIMARY KEY IDENTITY,
    fullname VARCHAR(100) NOT NULL
);

سوم، چند ردیف درون جداول candidates و employees وارد می‌کنیم:

INSERT INTO 
    hr.candidates(fullname)
VALUES
    ('John Doe'),
    ('Lily Bush'),
    ('Peter Drucker'),
    ('Jane Doe');
 
 
INSERT INTO 
    hr.employees(fullname)
VALUES
    ('John Doe'),
    ('Jane Doe'),
    ('Michael Scott'),
    ('Jack Sparrow');

حالا فرض کنیم که جدول Candidates جدول سمت چپ و جدول employees جدول سمت راست ما هستند.

دستور Inner Join در SQL Server

Inner join مجموعه داده‌ای تولید می‌کند که شامل ردیف‌هایی از جدول سمت چپ است که این جدول دارای ردیف‌ها مشابه در جدول سمت راست است.

مثال زیر از دستور inner join برای به دست آوردن ردیف‌ها از جدول candidates استفاده می‌کند که این جدول دارای ردیف‌های مشابه و دارای مقادیر یکسان در ستون fullname از جدول employees هستند:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    INNER JOIN hr.employees e 
        ON e.fullname = c.fullname;

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

نتایج

نمودار دایره‌ای زیر نتیجه دستور inner join در دو مجموعه نتایج را نمایش می‌دهد:

نمودار

دستور LEFT JOIN در SQL Sever

دستور LEFT JOIN داده‌ها را از جدول سمت چپ انتخاب می‌کند و آن‌ها را با ردیف‌های جدول سمت راست تطابق می‌دهد. دستور LEFT JOIN تمام ردیف‌های جدول سمت چپ و ردیف‌های مشابه از جدول سمت راست را بازمی‌گرداند.

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

دستور LEFT JOIN همچنین با نام LEFT OUTER JOIN نیز شناخته می‌شود. استفاده از کلیدواژه outer اختیاری است.

کد زیر جدول candidates را با استفاده از دستور left join به دستور employees متصل می‌کند:

SELECT  
 c.id candidate_id,
 c.fullname candidate_name,
 e.id employee_id,
 e.fullname employee_name
FROM 
 hr.candidates c
 LEFT JOIN hr.employees e 
 ON e.fullname = c.fullname;

نتایج به این شکل هستند:

نتایج

نمودار دایره‌ای زیر نتایج دستور LEFT JOIN را برای دو مجموعه نتیجه نمایش می‌دهد:

نمودار

برای به دست آوردن ردیف‌هایی که فقط در جدول سمت چپ وجود دارند، اما در جدول سمت راست نه، یک دستور where به query بالا اضافه می‌کنیم:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    LEFT JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE 
    e.id IS NULL;

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

نتایج

و نمودار دایره‌ای زیر، نتایج دستور LEFT JOIN را نشان می‌دهد که این دفعه ردیف‌هایی که فقط در جدول سمت چپ وجود دارند را انتخاب کرده است:

نتایج

دستور RIGHT JOIN در SQL Server

دستور right join یا right outer join داده‌ها را ابتدا از جدول سمت راست انتخاب می‌کند. این دستور دقیقاً برعکس دستور LEFT JOIN عمل می‌کند.

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

مثال زیر از دستور right join برای query ردیف‌های جداول candidates و employees استفاده می‌کند:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    RIGHT JOIN hr.employees e 
        ON e.fullname = c.fullname;

نتایج به این شکل هستند:

نتایج

توجه کنید که همه ردیف‌ها از جدول سمت راست (یعنی employees) در مجموعه نتایج درج شده‌اند. نمودار دایره‌ای زیر نتیجه دستور right join را برای دو مجموعه نتیجه نمایش می‌دهد:

نمودار

به طور مشابه، می‌توانید ردیف‌هایی که فقط در جدول سمت راست هستند را با استفاده از یک دستور WHERE در query بالا به دست آورید که در کد زیر مشاهده می‌کنید:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    RIGHT JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE
    c.id IS NULL;

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

نتایج

و نمودار دایره‌ای زیر این عملیات را نمایش می‌دهد:

نمودار

دستور full join در SQL Server

دستور full outer join یا full join مجموعه‌ای از نتایج را بازمی‌گرداند که شامل تمام ردیف‌ها از هر دو جداول چپ و راست و همچنین ردیف‌های مشابه (اگر در جداول وجود داشته باشند) است. اگر هیچ ردیف مشابهی وجود نداشت، سمتی که ردیف کم دارد، مقدار null می‌گیرد.

مثال زیر نشان می‌دهد که چگونه می‌توانید یک دستور full join بین جداول candidates و employees اجرا کنید:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    FULL JOIN hr.employees e 
        ON e.fullname = c.fullname;

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

نتایج

نمودار دایره‌ای زیر عملکرد دستور full outer join را نمایش می‌دهد:

نمودار

برای انتخاب ردیف‌هایی که یا در جدول چپ یا در جدول راست قرار دارند، باید ردیف‌هایی که در هر دو جدول مشابه هستند را با افزودن یک دستور WHERE حذف کنید:

SELECT  
    c.id candidate_id,
    c.fullname candidate_name,
    e.id employee_id,
    e.fullname employee_name
FROM 
    hr.candidates c
    FULL JOIN hr.employees e 
        ON e.fullname = c.fullname
WHERE
    c.id IS NULL OR
    e.id IS NULL;

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

نتایج

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

نمودار

بسیار خب، با انواع دستورهای join در SQL Server آشنا شدید. اما مطمئناً این مثال‌ها و توضیحات سریع کافی نیستند. ما صرفاً می‌خواستیم با syntax آن‌ها آشنا شوید. در ادامه به صورت مفصل و پروژه محور تک‌تک دستورات بالا را توضیح خواهیم داد.

دستور INNER JOIN در  SQL Server

این دستور یکی از مرسوم‌ترین دستور‌های join در SQL Server است. دستور INNER JOIN به شما امکان می‌دهد تا داده‌ها را از دو یا تعداد بیشتری جدول به هم متصل query نویسی کنید.

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

جداول products و categories

کد زیر اطلاعات محصول را از جدول produuction.products را دریافت می‌کند:

SELECT
    product_name,
    list_price,
    category_id
FROM
    production.products
ORDER BY
    product_name DESC;

نتایج

Query فقط لیستی از شماره‌های id دسته‌بندی را بازگرداند، نه نام‌های دسته‌ها را. برای درج نام دسته‌ها در مجموعه نتایج، می‌توانید به شکل زیر از دستور INNER JOIN استفاده کنید:

SELECT
    product_name,
    category_name,
    list_price
FROM
    production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id
ORDER BY
    product_name DESC;

نتایج

در این query، دستور inner join ردیف‌های هر دو جدول‌های products و categories را با هم تطابق می‌دهد.

اگر مقدار یک ردیف در جدول products در ستون category id با مقدار ردیف دارای همان ستون در جدول Categories یکسان باشد، query مقادیر ستون‌های مشخص شده در لیست Select را درون یک ردیف جدید ترکیب کرده و آن ردیف جدید را در مجموعه نتایج نمایش می‌دهد.

Syntax دستور INNER JOIN در SQL Server

کد زیر syntax دستور INNER JOIN در SQL Server را نمایش می‌دهد:

SELECT
    select_list
FROM
    T1
INNER JOIN T2 ON join_predicate;

در این syntax، کد داده‌ها را از هر دو جداول T1 و T2 دریافت می‌کند:

  • ابتدا، جدول اصلی (T1) در دستور FROM مشخص می‌شود.
  • سپس، جدول ثانویه و یک شرط اتصال در دستور INNER JOIN (یعنی T2) مشخص می‌شوند. فقط ردیف‌هایی که باعث شوند نتیجه ارزیابی شرط اتصال TRUE شود، در مجموعه نتایج قرار می‌گیرند.

دستور INNER JOIN هر ردیف از جدول T1 را با ردیف‌های جدول T2 مقایسه می‌کند تا تمام جفت ردیف‌هایی که باعث می‌شوند نتیجه شرط true شود را پیدا کنند. اگر نتیجه شرط اتصال TRUE باشد، مقادیر ستون در ردیف‌های مشابه جداول T1 و T2، درون یک ردیف جدید با هم ترکیب می‌شوند و در مجموعه نتایج درج می‌شوند.

تصویر زیر کاربرد دستور INNER JOIN برای دو جدول (T1(1,2,3 و (T2(A,B,C را نشان می‌دهد. نتیجه شامل ردیف‌های (2,A) و (3,B) است چون دارای الگوهای یکسان هستند.

کاربرد دستور INNER JOIN

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

SELECT
    select_list
FROM
    T1
JOIN T2 ON join_predicate;

یک مثال‌ inner join دیگر در SQL Server

جداول categories، products و brands را مشاهده کنید:

جداول categories، products و brands

کد زیر از دو دستور INNER JOIN برای به دست آوردن داده‌ها از سه جدول استفاده کرده‌ است:

SELECT
    product_name,
    category_name,
    brand_name,
    list_price
FROM
    production.products p
INNER JOIN production.categories c ON c.category_id = p.category_id
INNER JOIN production.brands b ON b.brand_id = p.brand_id
ORDER BY
    product_name DESC;

نتایج

بسیار خب، حالا دیگر به طور کامل INNER JOIN و عملکرد آن را درک کردید و حالت‌های مختلف به کار بردن آن را مشاهده کردید.

دستور Left Join در SQL Server

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

SELECT
    select_list
FROM
    T1
LEFT JOIN T2 ON
    join_predicate;

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

برای هر ردیف از جدول T1، کد آن را با تمام ردیف‌های جدول T2 مقایسه می‌کند. اگر یک جفت از ردیف‌ها باعث شوند شرط اتصال TRUE شود، مقادیر ستون موجود در این دو ردیف‌ در یک ردیف جدید ترکیب شده و در مجموعه نتایج نمایش داده می‌شوند.

اگر یک ردیف از جدول سمت چپ (T1) هیچ ردیف مشترکی در جدول T2 نداشته باشد، آنگاه کد مقادیر ستون موجود در ردیف جدول سمت چپ را به ازای هر مقدار ستون از جدول سمت راست با null ترکیب می‌کند.

به طور خلاصه، دستور LEFT JOIN تمام ردیف‌ها را از جدول سمت چپ (T1) با ردیف‌های جدول سمت راست (T2) تطابق می‌دهد و اگر تشابهی وجود نداشت، ردیف جدید در مجموعه نتایج با null ترکیب شده و نمایش داده می‌شود.

تصویر زیر کاربرد دستور LEFT JOIN را برای جداول (T1(1,2,3 و (T2(A,B,C نمایش می‌دهد:

کاربرد دستور left join

در این تصویر، هیچ ردیفی از جدول T2 با ردیف 1 از جدول T1 تطابق ندارد، در نتیجه از NULL استفاده شده است. ردیف 2 و 3 از جدول T1 با ردیف A و B از جدول T2 تطابق دارند.

مثال LEFT JOIN در SQL Server

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

جداول products و order_items

هر آیتم سفارش فروش شامل یک محصول است. ارتباط بین جداول order_items و products از طریق مقادیر موجود در ستون product_id ایجاد شده است.

کد زیر از دستور LEFT JOIN برای به دست آوردن داده از جداول products و order_items استفاده کرده است:

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

نتایج

همان‌طور که به وضوح از مجموعه نتایج مشخص است، لیستی از NULL در ستون order_id نشان‌دهنده این است که محصولات مشابه هنوز به هیچ مشتری فروخته نشده‌اند.

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

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

نتایج

مثل همیشه، SQL Server دستور WHERE را بد از دستور LEFT JOIN پردازش می‌کند.

شروط دستور LEFT JOIN در دستور ON در برابر دستور WHERE

Query زیر محصولاتی را می‌یابد که به id سفارش شماره ۱۰۰ تعلق دارند:

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

نتایج

حالا اجازه دهید شرط order_id = 100 را به دستور ON منتقل کنیم:

SELECT
    p.product_id,
    product_name,
    order_id
FROM
    production.products p
LEFT JOIN sales.order_items o ON o.product_id = p.product_id AND p.product_id = 100
ORDER BY
    order_id DESC;

نتایج

Query تمام محصولات را بازگرداند، اما فقط محصول با id‌ شماره ۱۰۰ دارای داده‌های مرتبط با سفارش مورد نظر می‌باشد.

توجه کنید که برای دستور INNER JOIN، شرط در دستور ON از لحاظ کاربردی با استفاده از آن در دستور WHERE یکسان است.

خلاصه

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

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

در جلسه بعدی دستورات CROSS HOIN، FULL OUTER JOIN، RIGHT JOIN و روش self join بررسی خواهند شد.

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

منبع: sqlservertutorial

 مطالب مرتبط

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

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

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

آخرین مطالب

آموزش جامع SQL Server (جلسه ۱۲)
آموزش جامع SQL Server (جلسه ۱۲)

دستور UPDATE در SQL Server برای تغییر داده‌های موجود در یک جدول، از دستور UPDATE به شکل زیر استفاده ...

آموزش جامع SQL Server (جلسه ۱۵)
آموزش جامع SQL Server (جلسه ۱۵)

دستور DROP TABLE در SQL Server گاهی، لازم است یک جدول که دیگر استفاده‌ای ندارد را حذف کنید. برای ...

آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)
آموزش جامع SQL Server (جلسه ۳۵: Window Functionها – بخش ۲)

بخش اول از آخرین مبحث دوره جامع آموزش SQL Server در جلسه قبلی بررسی شد. این مبحث که ...

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

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

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