Join (SQL)
SQL join օպերանդը համատեղում է տվյալների բազայի սյուները մեկ կամ մի քանի աղյուսակներից ռելացիոն (հարաբերական) տվյալների բազաներում (ՏԲ)։ Դա ստեղծում է հավաքածու, որ կարող է պահպանել որպես նոր աղյուսակ կամ որ օգտագործեք այն։ JOIN
օպերանդը օգտագործվում է մի կամ մի քանի տվյալների բազաների աղյուսակների ընդհանուր տվյալները միավորելու համար։ ANSI ընկերությունը մատնանշում է SQL ՏԲ-ի JOIN
օպերանդի հինգ տեսակ՝ INNER
, LEFT OUTER
, RIGHT OUTER
, FULL OUTER
and CROSS
: Որպես մասնավոր դեպք կարող է պատահել նաև աղյուսակի JOIN
օպերանդով «ինքն իրեն» միանալը։
Տեսակ | binary operator?, relational algebra operator? և SQL keyword? |
---|
Ծրագրավորողը հայտարարում է JOIN
օպերանդը միանման տողերը միավորելու համար։ Եթե ստեղծված նախատիպը ճիշտ է, ապա համատեղված տողերը կամ պահվում են համապատասխան ֆորմատով, կամ էլ նոր տողի կամ ժամանակավոր աղյուսակի տեսքով։
Աղյուսակների օրինակներ
խմբագրելՌելացիոն (հարաբերական) ՏԲ-ները սովորաբար կարգավորում են աղյուսակները, այսինքն վերացնում են կրկնօրինակները, երբ տիպերը կապված են լինում մեկը-շատին կապերով։ Օրինակի համար, բաժինը կարող է կապված լինել աշխատակիցների քանակի հետ։ Միավորելով բաժնի և աշխատակիցների առանձին աղյուսակները, կստեղծվի նոր աղյուսակ, որտեղ երկու աղյուսակներից տվյալները արդյունավետորեն համատեղված են։
Միավորման տեսակները բացատրելու համար բերված են օրինակներ երկու աղյուսակներից։ Այս աղյուսակների տողերը ծառայում են միավորման
տարբերի տիպերի և միավորման տիպերի բացատրության համար։ Հետևյալ աղյուսակներում DepartmentID
սյունը Department
աղյուսակից բանալի դաշտ է։
LastName | DepartmentID |
---|---|
Rafferty | 31 |
Jones | 33 |
Heisenberg | 33 |
Robinson | 34 |
Smith | 34 |
Williams | null
|
DepartmentID | DepartmentName |
---|---|
31 | Sales |
33 | Engineering |
34 | Clerical |
35 | Marketing |
Նշում. Employee աղյուսակում աշխատակից "Williams"ը դեռ ոչ մի բաժնում չի գրանցվել։ Ինչպես նաև ոչ մի աշխատակից չի գրանցվել "Marketing" բաժնում։
Սա SQL հայտարարություն է վերոհիշյալ աղյուսակները ստեղծելու համար։
CREATE TABLE department
(
DepartmentID INT Primary key,
DepartmentName VARCHAR(20)
);
CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT references department(DepartmentID)
);
INSERT INTO department VALUES(31, 'Sales');
INSERT INTO department VALUES(33, 'Engineering');
INSERT INTO department VALUES(34, 'Clerical');
INSERT INTO department VALUES(35, 'Marketing');
INSERT INTO employee VALUES('Rafferty', 31);
INSERT INTO employee VALUES('Jones', 33);
INSERT INTO employee VALUES('Heisenberg', 33);
INSERT INTO employee VALUES('Robinson', 34);
INSERT INTO employee VALUES('Smith', 34);
INSERT INTO employee VALUES('Williams', NULL);
Cross join (խաչաձև միավորում)
խմբագրելCROSS JOIN օպերանդը վերադարձնում է աղյուսակում միավորման ժամանակ բաղ թողնված տողերը։ Այլ կերպ ասած, այն ստեղծում է տողեր, որտեղ համատեղվում են արդյունքներ առաջին աղյուսակի յուրաքանչյուր տողի հետ երկրորդ աղյուսակից[1]։
Պարզ cross join միավորման օրինակ՝
SELECT *
FROM employee CROSS JOIN department;
Անիմաստ cross join միավորման օրինակ՝
SELECT *
FROM employee, department;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Rafferty | 31 | Sales | 31 |
Jones | 33 | Sales | 31 |
Heisenberg | 33 | Sales | 31 |
Smith | 34 | Sales | 31 |
Robinson | 34 | Sales | 31 |
Williams | null |
Sales | 31 |
Rafferty | 31 | Engineering | 33 |
Jones | 33 | Engineering | 33 |
Heisenberg | 33 | Engineering | 33 |
Smith | 34 | Engineering | 33 |
Robinson | 34 | Engineering | 33 |
Williams | null |
Engineering | 33 |
Rafferty | 31 | Clerical | 34 |
Jones | 33 | Clerical | 34 |
Heisenberg | 33 | Clerical | 34 |
Smith | 34 | Clerical | 34 |
Robinson | 34 | Clerical | 34 |
Williams | null |
Clerical | 34 |
Rafferty | 31 | Marketing | 35 |
Jones | 33 | Marketing | 35 |
Heisenberg | 33 | Marketing | 35 |
Smith | 34 | Marketing | 35 |
Robinson | 34 | Marketing | 35 |
Williams | null |
Marketing | 35 |
Cross join միավորումը ինքնուրույն որևէ բան չի կիրառում միավորման աղյուսակում տողերը զտելու համար։ Աղյուսակում արդյունքները զտելու համար կիրառվում է WHERE
օպերանդը, որը կարող է նաև լինել համարժեք inner join միավորմանը։
Սովորաբար օգտագործվում է սերվերի աշխատանքը ստուգելու համար։
Inner join (ներքին միավորում)
խմբագրելInner join օպերանդը պահանջում է երկու միավորված աղյուսակների յուրաքանչյուր տող ունենա սյունին համապատասխան արժեքներ, այն ընդհանրապես օգտագործվում է հայտերի միավորման ժամանակ, բայց բոլոր իրավիճակներում կիրառվող լավագույն ձևը չէ։ Inner join գործողությունը ստեղծում է նոր աղյուսակ երկու աղյուսակների (A և B) ընդհանուր տվյալները միավորելով։ Երբ ստեղծված նախատիպում արժեքները դատարկ (non-NULL) չեն լինում, այդ դեպքում սյուների արժեքները, որոնք բավարարում են դատարկ չլինելու պահանջին համատեղվում են նոր տողում։
SQL-ին հատուկ է միավորման երկու ձև՝ «պարզ (իմաստալից)» և «անիմաստ»։ «Անիմաստ» միավորումը որ երկար քննարկելու արդյունքն է, չնայած դրան ՏԲ-ն աջակցում է նմանատիպ հարցումներին։
«Պարզ (իմաստալից)» հարցման օրինակ է հետևյալը՝
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON
employee.DepartmentID = department.DepartmentID
Employee.LastName | Employee.DepartmentID | Department.DepartmentName |
---|---|---|
Robinson | 34 | Clerical |
Jones | 33 | Engineering |
Smith | 34 | Clerical |
Heisenberg | 33 | Engineering |
Rafferty | 31 | Sales |
Այս հարցման օրինակը համարժեք է նախորդին, բայց այս անգամ օգտագործվել է «անիմաստ» հարցում
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Վերևում բերված հարցումները միավորում են Employee և Department աղյուսակները օգտագործելով երկու աղյուսակների DepartmentID սյունը։ Այնտեղ, որտեղ այս աղյուսակների DepartmentID դաշտերը համընկնում են, հարցումը միավորում է LastName(անուն), DepartmentID(բաժնի համար) and DepartmentName(բաժնի անուն) սյուները երկու աղյուսակներից մի տողում։ Իսկ որտեղ արդյունքները չեն համապատասխանում ոչ մի արդյունք չի գրանցվում։
Ահա և հարցման արդյունքը՝
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Robinson | 34 | Clerical | 34 |
Jones | 33 | Engineering | 33 |
Smith | 34 | Clerical | 34 |
Heisenberg | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
Աշխատակից "Williams"-ը և "Marketing" բաժինը հարցման արդյունքներում չկան, քանի որ ոչ մի համընկնում չկա. "Williams"-ը չունի բաժին, իսկ 35 ("Marketing") բաժինը չունի որևէ աշխատակից։ Կախված ցանկալի արդյունքներից inner join միավորումը կարելի է փոխարինել outer join միավորմամբ։
Ծրագրավորողները պետք է միավորման ժամանակ հատուկ ուշադրություն դարձնեն այն սյուներին, որոնք դատարկ (NULL) արժեքներ են պարունակում, քանի որ միավորման ժամանակն առաջինը ստուգվում է տողի ոչ դատարկ լինելը, նույնիսկ եթե երկուսն էլ դատարկն են, միավորում չի լինում։ Inner join միավորումը կարող է միայն ապահով օգտագործվել ՏԲ-ի ամբողջականությունը պահպանելու համար կամ երբ միավորված սյուները երաշխավորված են դատարկ լինելուց։ Շատ տրանզակցիոն գործարքներ ռելացիոն բազաներում հիմնված են հատուկ ACID տվյալների թարմացման ստանդարտների ամբողջականությունը պահպանելու վրա։ Ինչևէ տրանզակցիոն ՏԲ-ները սովորաբար նաև ունենում են ցանկալի միավորվող սյուներ, որոնք արժեքները թույլատրվում են լինել NULL: Բազմաթիվ հաշվետու ռելացիոն ՏԲ-ներ և տվյալների պահեստներ ունեն բարձր ապահովվածության աստիճան, որը դժվար կամ նույնիսկ անհնար է դարձնում SQL հարցում կատարողին միավորել կամ փոփոխել դատարկ արժեք ունեցող սյուները։
Ցանկացած տվյալի սյուն, որը կարող է լինել դատարկ(NULL) չպիտի օգտագործվի inner join միավորման ժամանակ որպես հղում, եթե նախատեսվում է դատարկ արժեք ունեցող սյուները վերացնել։ Եթե NULL միավորված սյուները պետք է հեռացվեն արդյունքից, inner join միավորումը կարող է լինել ավելի արագ, քան outer join միավորումը, քանի որ աղյուսակի միավորումը և զտումը տեղի է ունենում քայլ առ քայլ։ Եվ ընդհակառակը, երբեմն կարող է outer join միավորումը լինել ավելի արագ, քան inner join միավորումը, եթե օգտագործվեն ագրեգացված ֆունկցիաներ, օրինակ SQL Where[2][3][4]: SQL Where ֆունկցիան կարող է պատճառ հանդիսանալ, որ ՏԲ-ն անտեսի կապված աղյուսակների ինդեքսները։ ՏԲ-ն կարող է կարդալ և inner join անել ընտրված սյուները երկու աղյուսակներից էլ նախքան զտման համար օգտագործվող տողերի քանակը հաշվելը։
Inner join միավորումը կարելի է դասակարգել որպես equi-joins, որպես natural joins, կամ որպես cross-joins.
Equi-join(հավասար միավորում)
խմբագրելEqui-join միավորումը հատուկ համեմատական տիպ է, որը կիրառվում է միայն հավասարություն ստուգելու և աղյուսակները միավորելու համար։ Օգտագործելով այլ համեմատության օպերատորներ(ինչպես օրինակ <
) չի կարելի equi-join կատարել։ ՆԵրքևում ցույց է տրված equi-join միավորման օրինակ՝
SELECT *
FROM employee JOIN department
ON employee.DepartmentID = department.DepartmentID;
Կարող ենք գրել equi-join միավորում, ինչպես գրված է ներքևում՝
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;
Եթե սյուները equi-join միավորման ժամանակ ունեն նույն անունը , ապա SQL-92 ստանդարտը ապահովում է օպտիմալ կիրառվող տարբերակ՝ կատարել նշում , օգտագործելով USING
կառուցվածքը[5]։
SELECT *
FROM employee INNER JOIN department USING (DepartmentID);
Ներքևի օրինակում DepartmentID
սյունը կլինի մեկը, քանի որ կան employee.DepartmentID
և department.DepartmentID
սյուներ։
USING
կառուցվածքը չի աջակցվում by MS SQL Server և Sybase ՏԲ-ների կողմից։
Natural join(բնական միավորում)
խմբագրելNatural join միավորումը equi-join միավորման հատուկ տիպ է։ Natural join (⋈) միավորումը երկուական գործողություն է, որը գրվում է որպես (R ⋈ S) , որտեղ R և S տառերը ռելացիոն բազաներ են[6]։ Natural join միավորման արդյունքը համատեղված տողերն են R և S ՏԲ-ներից, որոնք հավասար են իրենց ատրիբուտի անունին։ Օրինակի համար բերված է Employee և Dept աղյուսակները և նրանց natural join միավորումը՝
|
|
|
Outer join(արտաքին միավորում)
խմբագրելՄիավորված աղյուսակները պահում են յուրաքանչյուր տող նույնիսկ եթե համապատասխանող տող գոյություն չունի։ Outer join միավորումը լինում է left outer join, right outer join, և full outer join, կախված, թե որ աղյուսակի տողերն են պահվում (ձախ, աջ, թե երկուսն էլ)։
Ստանդարտ SQL լեզվում չկան outer join միավորման անիմաստ տիպեր։
Left outer join(ձախ արտաքին միավորում)
խմբագրելLeft outer join (կամ պարզ left join) A և B աղյուսակների համար միշտ պարունակում է աջ աղյուսակի(A) բոլոր տողերը, նույնիսկ եթե միավորելու պայմանի մեջ որևէ համապատասխանություն չի գտնում աջ(B) աղյուսակի հետ:Սա նշանակում է, որ եթե ON
կետով ոչ մի տող չի համապատասխանում ON
B աղյուսակի տողերի հետ , միավորումը կվերադարձնի արդյունք, բայց B աղյուսակի դատարկ տողերով։ A Left outer join վերադարձնում է inner join միավորման ժամանակ կատարված բոլոր արժեքները գումարած ձախ աղյուսակի այն արժեքները, որոնք աջ աղյուսակի չեն համընկնում, ներառյալ դատարկ արժեք ունեցող սյուները։
Օրինակ, սա թույլ է տալիս գտնել աշխատակցի բաժինը, բայց ցույց է տալիս այն աշխատակիցներին, ովքեր դեռ որևէ բաժնում չեն գրանցված չեն։
Left outer join միավորման օրինակ՝
SELECT *
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Jones | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
Robinson | 34 | Clerical | 34 |
Smith | 34 | Clerical | 34 |
Williams | null |
null |
null
|
Heisenberg | 33 | Engineering | 33 |
Այլընտրանքային տարբերակ
խմբագրելOracle ՏԲ-ն աջակցում է հետևյալ հայտարարված[7] տարբերակին՝
SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)
Sybase ՏԲ-ն աջակցում է հետևյալ տարբերակին՝ Microsoft SQL Server-ում այս տարբերակը հայտարարվում է սկսած 2000 թվականից)՝
SELECT *
FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID
IBM Informix աջակցում է հետևյալ տարբերակին՝
SELECT *
FROM employee, OUTER department
WHERE employee.DepartmentID = department.DepartmentID
Right outer join(աջ արտաքին միավորում)
խմբագրելRight outer join (կամ right join) մոտավոր հիշեցնում է left outer join միավորմանը, բացի աղյուսակների մշակումներից հետո նրանց հետ վերադարձմանը։ Ամեն տող աջ աղյուսակից(B) կհայտնվի միացված աղյուսակում գոնե մեկ անգամ։ Եթե ձախ աղյուսակի(A) հետ որևէ համընկնում չկա, ապա աղյուսակում կհայտնվեն դատարկ արժեքներ A աղյուսակի այն տողերի համար, որոնք B աղյուսակում չկան։
Ահա right outer join միավորման օրինակ՝
SELECT *
FROM employee RIGHT OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Smith | 34 | Clerical | 34 |
Jones | 33 | Engineering | 33 |
Robinson | 34 | Clerical | 34 |
Heisenberg | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
null |
null |
Marketing | 35 |
Right outer join և left outer join միավորումները ֆունկցիոնալորեն համարժեք են։ Որևէ մեկը ֆունկցիոնալությամբ մյուսից առավել չէ, այսինքն մեկը մյուսին կարող է փոխարինել, եթե աղյուսակները տեղերով փոխարինենք։
Full outer join(լրիվ արտաքին միավորում)
խմբագրելԱյս միավորումը միավորում է left outer join և right outer join միավորումները։ Եթե արդյունքները չեմ համապատասխանում միավորման ժամանակ, ապա աղյուսակում դատարկ արժեքներ են լրացվում։
Օրինակ սա թույլ է տալիս տեսնել բոլոր այն աշխատակիցներին, ովքեր գրանցված են որևէ բաժնում կամ յուրաքանչյուր բաժին, որ ունի աշխատակից, ինչպես նաև տեսնել այն բաժինը, որը ոչ մի աշխատակից չունի և տեսնել այն աշխատակցին, որը որևէ բաժնում գրանցված չէ։
Full outer join միավորման օրինակ(the OUTER
keyword is optional)՝
SELECT *
FROM employee FULL OUTER JOIN department
ON employee.DepartmentID = department.DepartmentID;
Employee.LastName | Employee.DepartmentID | Department.DepartmentName | Department.DepartmentID |
---|---|---|---|
Smith | 34 | Clerical | 34 |
Jones | 33 | Engineering | 33 |
Robinson | 34 | Clerical | 34 |
Williams | null |
null |
null
|
Heisenberg | 33 | Engineering | 33 |
Rafferty | 31 | Sales | 31 |
null |
null |
Marketing | 35 |
Որոշ ՏԲ-ներ չեն աջակցում full outer join միավորման ֆունկցիոնալությանը ամբողջապես, բայց դրանք կարող են նմանվել inner join և UNION ALL ընտրությունների օգտագործմանը։ Ահա և օրինակը՝
SELECT employee.LastName, employee.DepartmentID,
department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.LastName, employee.DepartmentID,
cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
SELECT * FROM department
WHERE employee.DepartmentID = department.DepartmentID)
UNION ALL
SELECT cast(NULL as varchar(20)), cast(NULL as integer),
department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
SELECT * FROM employee
WHERE employee.DepartmentID = department.DepartmentID)
Self-join (ինքնամիավորում)
խմբագրելԱյս տեսակը միավորում է աղյուսակը ինքն իր հետ[8]։
Օրինակ
խմբագրելԵթե կան երկու առանձին աղյուսակներ աշխատակիցների համար և հարցում, որտեղ անհրաժեշտ են առաջին աղյուսակի այն աշխատակիցները, ովքեր ունեն նույն երկիրը, ինչ որ երկրորդ աղյուսակի աշխատակիցները, սովորական միավորման ժամանակ կարող ենք գտնել այդ հարցի պատասխանը։ Ինչևէ, բոլոր աշխատակիցների տվյալները կպահվեն նոր մեծ աղյուսակում[9]։
Փոփոխված Employee
աղյուսակը կունենա հետևյալ տեսքը՝
EmployeeID | LastName | Country | DepartmentID |
---|---|---|---|
123 | Rafferty | Australia | 31 |
124 | Jones | Australia | 33 |
145 | Heisenberg | Australia | 33 |
201 | Robinson | United States | 34 |
305 | Smith | Germany | 34 |
306 | Williams | Germany | null
|
Օրինակի լուծման հարցումը կլինի հետևյալը՝
SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;
Արդյունքները՝
EmployeeID | LastName | EmployeeID | LastName | Country |
---|---|---|---|---|
123 | Rafferty | 124 | Jones | Australia |
123 | Rafferty | 145 | Heisenberg | Australia |
124 | Jones | 145 | Heisenberg | Australia |
305 | Smith | 306 | Williams | Germany |
Այս օրինակի համար
F
ևS
կեղծանուններ են վերցված աշխատակիցների աղյուսակի առաջին և երկրորդ կրկնօրինակներից։F.Country = S.Country
պայմանը բացառում է տարբեր երկրներից աշխատակիցների զույգ լինելը։ Օրինակում քննարկվել է նույն երկրից աշխատակիցների զույգ կազմելու հարցը։F.EmployeeID < S.EmployeeID
պայմանը բացառում է այն զույգերին, որտեղ առաջին աշխատակցիEmployeeID
-ն ավելի մեծ է, քան երկրորդինը։ Այլ կերպ ասած, այս պայմանը բացառում է կրկօրինակ զույգերը կամ ինքն իր հետ զույգ կազմելը։ Առանց դրա, կստացվեր հետևյալ ոչ այնքան էլ օգտակար աղյուսակը՝
EmployeeID | LastName | EmployeeID | LastName | Country |
---|---|---|---|---|
305 | Smith | 305 | Smith | Germany |
305 | Smith | 306 | Williams | Germany |
306 | Williams | 305 | Smith | Germany |
306 | Williams | 306 | Williams | Germany |
Այլընտրանքներ
խմբագրելՕuter join միավորման օրինակ է նաև հետևյալը՝
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;
կարող է գրվել նաև՝
SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID
UNION ALL
SELECT employee.LastName, employee.DepartmentID, cast(NULL as varchar(20))
FROM employee
WHERE NOT EXISTS (
SELECT * FROM department
WHERE employee.DepartmentID = department.DepartmentID)
Ծանոթագրություններ
խմբագրել- ↑ SQL CROSS JOIN
- ↑ Greg Robidoux, "Avoid SQL Server functions in the WHERE clause for Performance", MSSQL Tips, 5/3/2007
- ↑ Patrick Wolf, "Inside Oracle APEX "Caution when using PL/SQL functions in a SQL statement", 11/30/2006
- ↑ Gregory A. Larsen, "T-SQL Best Practices - Don't Use Scalar Value Functions in Column List or WHERE Clauses", 10/29/2009,
- ↑ Simplifying Joins with the USING Keyword
- ↑ In Unicode, the bowtie symbol is ⋈ (U+22C8).
- ↑ «Oracle Left Outer Join». Արխիվացված է օրիգինալից 2017 թ․ հոկտեմբերի 7-ին. Վերցված է 2017 թ․ սեպտեմբերի 30-ին.
- ↑ Shah 2005, էջ. 165
- ↑ Adapted from Pratt 2005, էջեր. 115–6