Join (SQL)

Վիքիպեդիայից՝ ազատ հանրագիտարանից
Jump to navigation Jump to search

SQL join օպերանդը համատեղում է տվյալների բազայի սյուները մեկ կամ մի քանի աղյուսակներից ռելացիոն (հարաբերական) տվյալների բազաներում (ՏԲ): Դա ստեղծում է հավաքածու, որ կարող է պահպանել որպես նոր աղյուսակ կամ որ օգտագործեք այն: JOIN օպերանդը օգտագործվում է մի կամ մի քանի տվյալների բազաների աղյուսակների ընդհանուր տվյալները միավորելու համար: ANSI ընկերությունը մատնանշում է SQL ՏԲ-ի JOIN օպերանդի հինգ տեսակ՝ INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS: Որպես մասնավոր դեպք կարող է պատահել նաև աղյուսակի JOIN օպերանդով «ինքն իրեն» միանալը:

Ծրագրավորողը հայտարարում է JOIN օպերանդը միանման տողերը միավորելու համար: Եթե ստեղծված նախատիպը ճիշտ է, ապա համատեղված տողերը կամ պահվում են համապատասխան ֆորմատով, կամ էլ նոր տողի կամ ժամանակավոր աղյուսակի տեսքով:

Աղյուսակների օրինակներ[խմբագրել | խմբագրել կոդը]

Ռելացիոն (հարաբերական) ՏԲ-ները սովորաբար կարգավորում են աղյուսակները, այսինքն վերացնում են կրկնօրինակները, երբ տիպերը կապված են լինում մեկը-շատին կապերով: Օրինակի համար, բաժինը կարող է կապված լինել աշխատակիցների քանակի հետ: Միավորելով բաժնի և աշխատակիցների առանձին աղյուսակները, կստեղծվի նոր աղյուսակ, որտեղ երկու աղյուսակներից տվյալները արդյունավետորեն համատեղված են:

Միավորման տեսակները բացատրելու համար բերված են օրինակներ երկու աղյուսակներից: Այս աղյուսակների տողերը ծառայում են միավորման տարբերի տիպերի և միավորման տիպերի բացատրության համար: Հետևյալ աղյուսակներում DepartmentID սյունը Department աղյուսակից բանալի դաշտ է:

Employee table
LastName DepartmentID
Rafferty 31
Jones 33
Heisenberg 33
Robinson 34
Smith 34
Williams null
Department table
DepartmentID DepartmentName
31 Sales
33 Engineering
34 Clerical
35 Marketing

Նշում. Employee աղյուսակում աշխատակից "Williams"ը դեռ ոչ մի բաժնում չի գրանցվել: Ինչպես նաև ոչ մի աշխատակից չի գրանցվել "Marketing" բաժնում:

Սա SQL հայտարարություն է վերոհիշյալ աղյուսակները ստեղծելու համար:

 1 CREATE TABLE department
 2 (
 3  DepartmentID INT Primary key,
 4  DepartmentName VARCHAR(20)
 5 );
 6 
 7 CREATE TABLE employee
 8 (
 9  LastName VARCHAR(20),
10  DepartmentID INT references department(DepartmentID)
11 );
12 
13 INSERT INTO department VALUES(31, 'Sales');
14 INSERT INTO department VALUES(33, 'Engineering');
15 INSERT INTO department VALUES(34, 'Clerical');
16 INSERT INTO department VALUES(35, 'Marketing');
17 
18 INSERT INTO employee VALUES('Rafferty', 31);
19 INSERT INTO employee VALUES('Jones', 33);
20 INSERT INTO employee VALUES('Heisenberg', 33);
21 INSERT INTO employee VALUES('Robinson', 34);
22 INSERT INTO employee VALUES('Smith', 34);
23 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 (ներքին միավորում)[խմբագրել | խմբագրել կոդը]

A Venn Diagram showing the inner overlapping portion filled.
A և B աղյուսակների միջև Inner Join SQL հարցման դիագրամը

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 (⋈) միավորումը երկուական գործողություն է, որը գրվում է որպես (RS) , որտեղ R և S տառերը ռելացիոն բազաներ են[6]: Natural join միավորման արդյունքը համատեղված տողերն են R և S ՏԲ-ներից, որոնք հավասար են իրենց ատրիբուտի անունին: Օրինակի համար բերված է Employee և Dept աղյուսակները և նրանց natural join միավորումը՝

Employee
Name EmpId DeptName
Harry 3415 Finance
Sally 2241 Sales
George 3401 Finance
Harriet 2202 Sales
Dept
DeptName Manager
Finance George
Sales Harriet
Production Charles
Employee  Dept
Name EmpId DeptName Manager
Harry 3415 Finance George
Sally 2241 Sales Harriet
George 3401 Finance George
Harriet 2202 Sales Harriet

Outer join(արտաքին միավորում)[խմբագրել | խմբագրել կոդը]

Միավորված աղյուսակները պահում են յուրաքանչյուր տող նույնիսկ եթե համապատասխանող տող գոյություն չունի: Outer join միավորումը լինում է left outer join, right outer join, և full outer join, կախված, թե որ աղյուսակի տողերն են պահվում (ձախ, աջ, թե երկուսն էլ):

Ստանդարտ SQL լեզվում չկան outer join միավորման անիմաստ տիպեր:
A Venn Diagram showing the left circle and overlapping portion filled.
A և B աղյուսակների միջև Left Join SQL հարցման դիագրամը


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
A Venn Diagram show the right circle and overlapping portions filled.
A և B աղյուսակների միջև Right Join SQL հարցման դիագրամը


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 միավորումները ֆունկցիոնալորեն համարժեք են: Որևէ մեկը ֆունկցիոնալությամբ մյուսից առավել չէ, այսինքն մեկը մյուսին կարող է փոխարինել, եթե աղյուսակները տեղերով փոխարինենք:

A Venn Diagram showing the right circle, left circle, and overlapping portion filled.
A և B աղյուսակների միջև Full Join SQL հարցման դիագրամը

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 աղյուսակը կունենա հետևյալ տեսքը՝

Employee Table
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;

Արդյունքները՝

Employee Table after Self-join by Country
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)

Արտաքին հղումներ[խմբագրել | խմբագրել կոդը]

Ծանոթագրություններ[խմբագրել | խմբագրել կոդը]

  1. SQL CROSS JOIN
  2. Greg Robidoux, "Avoid SQL Server functions in the WHERE clause for Performance", MSSQL Tips, 5/3/2007
  3. Patrick Wolf, "Inside Oracle APEX "Caution when using PL/SQL functions in a SQL statement", 11/30/2006
  4. Gregory A. Larsen, "T-SQL Best Practices - Don't Use Scalar Value Functions in Column List or WHERE Clauses", 10/29/2009,
  5. Simplifying Joins with the USING Keyword
  6. In Unicode, the bowtie symbol is ⋈ (U+22C8).
  7. Oracle Left Outer Join
  8. Shah 2005, էջ. 165
  9. Adapted from Pratt 2005, էջեր. 115–6