Join (SQL)

Join
Տեսակ	binary operator? և relational algebra operator?
Ենթադաս	SQL keyword?
	SQL Joins Վիքիպահեստում

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 հայտարարություն է վերոհիշյալ աղյուսակները ստեղծելու համար։

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 միավորումը՝

*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* $\bowtie$ *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 միավորման անիմաստ տիպեր։

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

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)

Ծանոթագրություններ

↑ 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

Արտաքին հղումներ

[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).

[deprecated_plus_sign-7] «Oracle Left Outer Join». Արխիվացված է օրիգինալից 2017 թ․ հոկտեմբերի 7-ին. Վերցված է 2017 թ․ սեպտեմբերի 30-ին.

[8] Shah 2005, էջ. 165

[9] Adapted from Pratt 2005, էջեր. 115–6

[1]

[2]

[3]

[4]

[5]

[6]

[7]

[8]

[9]