SQL қосылымдары: түрлері және ескеретін жайттар

Кіріспе

SQL тармағы

Құрылымдық сұраныс тіліндегі (SQL) қосылу тармағы бір немесе бірнеше кестелердің бағандарын жаңа кестеге біріктіреді. Бұл операция реляциялық алгебрадағы қосылу операциясына сәйкес келеді. Жай тілмен айтқанда, қосылу екі кестені біріктіріп, сәйкес келетін өрістері бар жазбаларды бір қатарға орналастырады: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER және CROSS.

Ішкі қосылу және NULL мәндері

Бағдарламашылар NULL мәндері бар бағандарға кестелерді қосу кезінде ерекше сақ болуы керек, себебі NULL ешқандай басқа мәнмен (тіпті NULL өзімен де) сәйкес келмейді, егер қосылу шарты алдымен қосылатын бағандардың NULL емес екенін тексеріп, содан кейін қалған шарттарды қолданбайтын комбинациялық предикатты пайдаланбаса. Ішкі қосылу тек референциялық тұтастықты сақтайтын немесе қосылу бағандары NULL емес екеніне кепілдік берілген деректер базасында ғана қауіпсіз қолданылуы мүмкін. Көптеген транзакциялық реляциялық деректер базалары деректердің тұтастығын қамтамасыз ету үшін атомдық, консистенттілік, оқшаулау, беріктік (ACID) деректерді жаңарту стандарттарына сүйенеді, бұл ішкі қосылуды орынды таңдау жасайды. Алайда, транзакциялық деректер базаларында көбінесе NULL мәндеріне рұқсат етілген қосылу бағандары болады. Көптеген есептік реляциялық деректер базалары мен деректер қоймалары жоғары көлемді деректерді алу, түрлендіру және жүктеу (ETL) пакеттік жаңартуларын қолданады, бұл референциялық тұтастықты сақтауды қиын немесе мүмкін емес етеді, нәтижесінде SQL сұранысының авторы өзгерте алмайтын NULL қосылу бағандары пайда болады және ішкі қосылулар қате туралы ешқандай хабарламасыз деректерді жіберіп алады. Ішкі қосылуды қолдану туралы шешім деректер базасының дизайны мен деректердің ерекшеліктеріне байланысты. Егер бір кестедегі қосылу бағандарында NULL мәндері болуы мүмкін болса, сол жақ сыртқы қосылуды ішкі қосылудың орнына қолдану әдетте мүмкін. NULL (бос) болуы мүмкін кез келген деректер бағаны, егер мақсат NULL мәні бар қатарларды жою болмаса, ішкі қосылымдағы сілтеме ретінде қолданылмауы керек. Егер NULL қосылу бағандарын нәтиже жиынтығынан әдейі алып тастау қажет болса, ішкі қосылу сыртқы қосылудан жылдам болуы мүмкін, себебі кесте қосылу және сүзгілеу бір қадамда орындалады. Керісінше, үлкен көлемді сұрауларда SQL Where шарттарындағы деректер базасы функцияларымен бірге қолданылған ішкі қосылу өте баяу жұмыс істеуге немесе тіпті сервердің құлауына әкелуі мүмкін. SQL Where шарттарындағы функция деректер базасының салыстырмалы түрде ықшам кесте индекстерін елемеуіне себеп болуы мүмкін. Деректер базасы екі кестеден таңдалған бағандарды оқып, ішкі қосылуды орындауы мүмкін, содан кейін есептелген мәнге байланысты сүзгіні қолданып қатарлар санын азайтады, нәтижесінде тиімсіз өңдеудің үлкен көлемі пайда болады. Нәтиже жиынтығы бірнеше кестелерді қосу арқылы жасалғанда, оның ішінде сандық идентификатор кодтарының толық мәтіндік сипаттамаларын іздеуге арналған бас кестелер (алу кестесі) болса, шеткі кілттердің кез келгеніндегі NULL мәні қате туралы ешқандай хабарламасыз нәтиже жиынтығынан бүкіл қатарды жоюға әкелуі мүмкін. Бір немесе бірнеше ішкі қосылулар мен бірнеше сыртқы қосылуларды қамтитын күрделі SQL сұранысы ішкі қосылулар сілтемелерінің бағандарындағы NULL мәндері үшін де сол тәуекелді қамтиды. Ішкі қосылуларды қамтитын SQL кодына берілген міндеттеме NULL қосылу бағандары болашақтағы өзгерістермен, оның ішінде жеткізуші жаңартулары, дизайн өзгертулері және қосымшаның деректерді тексеру ережелерінен тыс жаппай өңдеу, мысалы, деректерді түрлендіру, көшіру, жаппай импорттау және біріктіру арқылы енгізілмейді деп есептейді. Ішкі қосылуларды экви қосылулар, табиғи қосылулар немесе қиылыс қосылулар деп жіктеуге болады.

Programmers should take special care when joining tables on columns that can contain NULL values, since NULL will never match any other value (not even NULL itself), unless the join condition explicitly uses a combination predicate that first checks that the joins columns are NOT NULL before applying the remaining predicate condition(s). The Inner Join can only be safely used in a database that enforces referential integrity or where the join columns are guaranteed not to be NULL. Many transaction processing relational databases rely on atomicity, consistency, isolation, durability (ACID) data update standards to ensure data integrity, making inner joins an appropriate choice. However, transaction databases usually also have desirable join columns that are allowed to be NULL. Many reporting relational database and data warehouses use high volume extract, transform, load (ETL) batch updates which make referential integrity difficult or impossible to enforce, resulting in potentially NULL join columns that an SQL query author cannot modify and which cause inner joins to omit data with no indication of an error. The choice to use an inner join depends on the database design and data characteristics. A left outer join can usually be substituted for an inner join when the join columns in one table may contain NULL values. Any data column that may be NULL (empty) should never be used as a link in an inner join, unless the intended result is to eliminate the rows with the NULL value. If NULL join columns are to be deliberately removed from the result set, an inner join can be faster than an outer join because the table join and filtering is done in a single step. Conversely, an inner join can result in disastrously slow performance or even a server crash when used in a large volume query in combination with database functions in an SQL Where clause. A function in an SQL Where clause can result in the database ignoring relatively compact table indexes. The database may read and inner join the selected columns from both tables before reducing the number of rows using the filter that depends on a calculated value, resulting in a relatively enormous amount of inefficient processing. When a result set is produced by joining several tables, including master tables used to look up full text descriptions of numeric identifier codes (a Lookup table), a NULL value in any one of the foreign keys can result in the entire row being eliminated from the result set, with no indication of error. A complex SQL query that includes one or more inner joins and several outer joins has the same risk for NULL values in the inner join link columns. A commitment to SQL code containing inner joins assumes NULL join columns will not be introduced by future changes, including vendor updates, design changes and bulk processing outside of the application's data validation rules such as data conversions, migrations, bulk imports and merges. One can further classify inner joins as equi joins, as natural joins, or as cross joins.

Сыртқы қосылу

Қосылған кестеде әрбір жол сақталады, тіпті егер басқа сәйкес келетін жол болмаса да. Сыртқы қосылыстар, қай кестедегі қатарлар сақталатынына байланысты, сол жақ сыртқы қосылыстарға, оң жақ сыртқы қосылыстарға және толық сыртқы қосылыстарға бөлінеді: сол, оң немесе екеуі де (бұл жағдайда «сол» және «оң» JOIN кілт сөзінің екі жағын білдіреді). Ішкі қосылыстар сияқты, сыртқы қосылыстардың барлық түрлерін экви-қосылыстар, табиғи қосылыстар, ON <шарт> (θ-қосылысы) және т.б. деп жіктеуге болады. Стандартты SQL-де сыртқы қосылыстар үшін ешқандай жасырын қосылу синтаксисі қарастырылмаған.

Өздігінен біріктірілетін

Өзін-өзі қосу – бір кестені өзімен-өзі біріктіру.

Алгоритмдерді қосу

Бинарлық қосылу операциясын орындау үшін үш негізгі алгоритм бар: ұялы циклдық қосылу, сұрыптау-біріктіру қосылу және хэш-қосылу. Ең нашар жағдайдағы оңтайлы біріктіру алгоритмдері, ең нашар жағдайда екіден көп қатынастарды біріктіргенде, бинарлық қосылу алгоритмдерінен асимптотикалық тұрғыдан жылдам болады.

Индекстерді қосу

Бірлесу индекстері – деректер қоймаларында біріктіру сұраныстарын өңдеуді жеңілдететін деректер базасының индекстері: олар қазіргі уақытта (2012) Oracle және Teradata жүйелерінде қолжетімді. Teradata жүйесінде бір немесе бірнеше кестелердегі белгілі бағандар, бағандардағы жиынтық функциялар немесе күндік бағандардың бөліктері деректер базасы көрінісін анықтау сияқты синтаксис арқылы көрсетіледі: бір бірлесу индексінде 64 бағанға/бағандық өрнекке дейін көрсетуге болады. Құрама деректің бірінші кілтін анықтайтын баған да көрсетілуі мүмкін: параллель жабдықталған жүйеде баған мәндері индекс мазмұнын бірнеше дискіге бөлу үшін пайдаланылады. Егер пайдаланушылар бастапқы кестелерді өзара әрекеттес түрде жаңартса, бірлесу индексінің мазмұны автоматты түрде жаңартылады. Егер сұраныстың WHERE шарттары бірлесу индексінде анықталған бағандардың немесе бағандық өрнектердің нақты кіші жиынын қамтыса (осылай аталатын «жабатын сұраныс»), сұранысты орындау кезінде бастапқы кестелер мен олардың индекстері емес, бірлесу индексі пайдаланылады. Oracle жүйесі биттік индекс пайдаланумен шектеледі. Биттік бірлесу индексі төмен кардиналдылыққа ие бағандар үшін қолданылады (яғни, Oracle құжаттамасына сәйкес, 300-ден аз ерекше мәні бар бағандар): ол бірнеше байланысты кестелерден төмен кардиналдылыққа ие бағандарды біріктіреді. Oracle мысалы ретінде инвентаризация жүйесін келтіреді, онда әртүрлі жеткізушілер әртүрлі бөлшектерді ұсынады. Схема үш байланысты кестеден тұрады: екі «негізгі кесте» – Бөлшек және Жеткізуші, және «қосымша кесте» – Инвентаризация. Соңғысы – Жеткізушіні Бөлшекке байланыстыратын көпке көп кесте, және ең көп қатарларды қамтиды. Әрбір бөлшектің Бөлшек түрі болады, ал әр жеткізуші АҚШ-та орналасқан және Штат бағанына ие. АҚШ-та 60 штаттан аспайтын және 300-ден аспайтын Бөлшек түрлері бар. Биттік бірлесу индексі жоғарыдағы үш кестедегі стандартты үш кестелік біріктіру арқылы анықталады, сондай-ақ индекс үшін Бөлшек түрі және Жеткізуші штаты бағандары көрсетіледі. Дегенмен, ол Инвентаризация кестесінде анықталады, тіпті Бөлшек түрі және Жеткізуші штаты бағандары сәйкесінше Жеткізуші және Бөлшек кестелерінен «қарызға алынған». Teradata-ға келетін болсақ, Oracle биттік бірлесу индексі тек сұранысқа жауап беру үшін қолданылады, егер сұраныстың WHERE шарты бірлесу индексіне енгізілген бағандармен шектелсе.

Join indexes are database indexes that facilitate the processing of join queries in data warehouses: they are currently (2012) available in implementations by Oracle and Teradata. In the Teradata implementation, specified columns, aggregate functions on columns, or components of date columns from one or more tables are specified using a syntax similar to the definition of a database view: up to 64 columns/column expressions can be specified in a single join index. Optionally, a column that defines the primary key of the composite data may also be specified: on parallel hardware, the column values are used to partition the index's contents across multiple disks. When the source tables are updated interactively by users, the contents of the join index are automatically updated. Any query whose WHERE clause specifies any combination of columns or column expressions that are an exact subset of those defined in a join index (a so called "covering query") will cause the join index, rather than the original tables and their indexes, to be consulted during query execution. The Oracle implementation limits itself to using bitmap indexes. A bitmap join index is used for low cardinality columns (i. e., columns containing fewer than 300 distinct values, according to the Oracle documentation): it combines low cardinality columns from multiple related tables. The example Oracle uses is that of an inventory system, where different suppliers provide different parts. The schema has three linked tables: two "master tables", Part and Supplier, and a "detail table", Inventory. The last is a many to many table linking Supplier to Part, and contains the most rows. Every part has a Part Type, and every supplier is based in the US, and has a State column. There are not more than 60 states+territories in the US, and not more than 300 Part Types. The bitmap join index is defined using a standard three table join on the three tables above, and specifying the Part Type and Supplier State columns for the index. However, it is defined on the Inventory table, even though the columns Part Type and Supplier State are "borrowed" from Supplier and Part respectively. As for Teradata, an Oracle bitmap join index is only utilized to answer a query when the query's WHERE clause specifies columns limited to those that are included in the join index.

Тіке қосылу

Кейбір деректер базасы жүйелері пайдаланушыға қосылыста кестелерді нақты бір ретпен оқуға жүйені мәжбүрлеуге мүмкіндік береді. Бұл, егер қосылу оптимизаторы кестелерді тиімсіз ретпен оқуды таңдаса қолданылады. Мысалы, MySQL-де STRAIGHT JOIN командасы кестелерді сұранышта тізілгендей нақты ретпен оқиды.