Използвате ли SQL подзаявки или избягвате да ги използвате?
Да приемем, че главният служител по кредити и събиране ви моли да изброите имената на хората, техните неплатени салда на месец и текущото текущо салдо и иска да импортирате този масив от данни в Excel. Целта е да се анализират данните и да се излезе с оферта, която да облекчи плащанията, за да смекчи ефектите от пандемията COVID19.
Избирате ли да използвате заявка и вложена подзаявка или съединение? Какво решение ще вземете?
SQL подзаявки – какво представляват те?
Преди да се потопим дълбоко в синтаксиса, въздействието върху производителността и предупрежденията, защо първо не дефинираме подзаявка?
Най-просто казано, подзаявка е заявка в заявка. Докато заявка, която включва подзаявка, е външната заявка, ние наричаме подзаявка вътрешна заявка или вътрешен избор. А скобите ограждат подзаявка, подобна на структурата по-долу:
SELECT
col1
,col2
,(subquery) as col3
FROM table1
[JOIN table2 ON table1.col1 = table2.col2]
WHERE col1 <operator> (subquery)В тази публикация ще разгледаме следните точки:
- Синтаксис на SQL подзаявка в зависимост от различните типове подзаявки и оператори.
- Кога и в какви изрази може да се използва подзаявка.
- Последици за производителността спрямо JOINs.
- Често срещани предупреждения при използване на SQL подзаявки.
Както е обичайно, ние предоставяме примери и илюстрации, за да подобрим разбирането. Но имайте предвид, че основният фокус на тази публикация е върху подзаявките в SQL Server.
А сега да започваме.
Направете SQL подзаявки, които са самостоятелни или корелирани
От една страна, подзаявките се категоризират въз основа на тяхната зависимост от външната заявка.
Нека опиша какво е самостоятелна подзаявка.
Самостоятелните подзаявки (или понякога наричани некорелирани или прости подзаявки) са независими от таблиците във външната заявка. Нека илюстрирам това:
-- Get sales orders of customers from Southwest United States -- (TerritoryID = 4)USE [AdventureWorks] GO SELECT CustomerID, SalesOrderID FROM Sales.SalesOrderHeader WHERE CustomerID IN (SELECT [CustomerID] FROM [AdventureWorks].[Sales].[Customer] WHERE TerritoryID = 4)
Както е показано в горния код, подзаявката (оградена в скоби по-долу) няма препратки към никоя колона във външната заявка. Освен това можете да маркирате подзаявката в SQL Server Management Studio и да я изпълните, без да получавате грешки по време на изпълнение.
Което от своя страна води до по-лесно отстраняване на грешки на самостоятелни подзаявки.
Следващото нещо, което трябва да имате предвид, са свързаните подзаявки. В сравнение със своя самостоятелен аналог, този има поне една колона, към която се препраща от външната заявка. За пояснение ще дам пример:
USE [AdventureWorks]
GO
SELECT DISTINCT a.LastName, a.FirstName, b.BusinessEntityID
FROM Person.Person AS p
JOIN HumanResources.Employee AS e ON p.BusinessEntityID = e.BusinessEntityID
WHERE 1262000.00 IN
(SELECT [SalesQuota]
FROM Sales.SalesPersonQuotaHistory spq
WHERE p.BusinessEntityID = spq.BusinessEntityID)Бяхте ли достатъчно внимателни, за да забележите препратката към BusinessEntityID от таблицата Person? Много добре!
След като колона от външната заявка бъде посочена в подзаявката, тя става корелирана подзаявка. Още един момент, който трябва да имате предвид: ако маркирате подзаявка и я изпълните, ще възникне грешка.
И да, вие сте абсолютно прав: това прави корелираните подзаявки доста по-трудни за отстраняване на грешки.
За да направите отстраняването на грешки възможно, изпълнете следните стъпки:
- изолирайте подзаявката.
- заменете препратката към външната заявка с постоянна стойност.
Изолирането на подзаявката за отстраняване на грешки ще я направи да изглежда така:
SELECT [SalesQuota]
FROM Sales.SalesPersonQuotaHistory spq
WHERE spq.BusinessEntityID = <constant value>Сега, нека надникнем малко по-дълбоко в изхода на подзаявките.
Направете SQL подзаявки с 3 възможни върнати стойности
Е, първо, нека помислим какви върнати стойности можем да очакваме от SQL подзаявки.
Всъщност има 3 възможни изхода:
- Една единствена стойност
- Множество стойности
- Цели маси
Единична стойност
Нека започнем с изход с една стойност. Този тип подзаявка може да се появи навсякъде във външната заявка, където се очаква израз, като клаузата WHERE.
-- Output a single value which is the maximum or last TransactionID
USE [AdventureWorks]
GO
SELECT TransactionID, ProductID, TransactionDate, Quantity
FROM Production.TransactionHistory
WHERE TransactionID = (SELECT MAX(t.TransactionID)
FROM Production.TransactionHistory t)Когато използвате функция MAX(), вие извличате една стойност. Точно това се случи с нашата подзаявка по-горе. Използването на оператора за равенство (=) казва на SQL Server, че очаквате една стойност. Друго нещо: ако подзаявката върне множество стойности, използвайки оператора за равенство (=), получавате грешка, подобна на тази по-долу:
Msg 512, Level 16, State 1, Line 20
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.Множество стойности
След това разглеждаме многозначния изход. Този вид подзаявка връща списък от стойности с една колона. Освен това оператори като IN и NOT IN ще очакват една или повече стойности.
-- Output multiple values which is a list of customers with lastnames that --- start with 'I'USE [AdventureWorks] GO SELECT [SalesOrderID], [OrderDate], [ShipDate], [CustomerID] FROM Sales.SalesOrderHeader WHERE [CustomerID] IN (SELECT c.[CustomerID] FROM Sales.Customer c INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID WHERE p.lastname LIKE N'I%' AND p.PersonType='SC')
Стойности на цялата таблица
И накрая, но не на последно място, защо не се задълбочите в резултатите от цялата таблица.
-- Output a table of values based on sales orders
USE [AdventureWorks]
GO
SELECT [ShipYear],
COUNT(DISTINCT [CustomerID]) AS CustomerCount
FROM (SELECT YEAR([ShipDate]) AS [ShipYear], [CustomerID]
FROM Sales.SalesOrderHeader) AS Shipments
GROUP BY [ShipYear]
ORDER BY [ShipYear]Забелязали ли сте клаузата ОТ?
Вместо да използва таблица, той използва подзаявка. Това се нарича извлечена таблица или подзаявка за таблица.
А сега нека ви представя някои основни правила при използване на този вид заявка:
- Всички колони в подзаявката трябва да имат уникални имена. Подобно на физическа таблица, производната таблица трябва да има уникални имена на колони.
- ПОРЪЧКА ПО не е разрешено, освен ако също не е посочено ГОРЕ. Това е така, защото извлечената таблица представлява релационна таблица, в която редовете нямат определен ред.
В този случай извлечената таблица има предимствата на физическа таблица. Ето защо в нашия пример можем да използваме COUNT() в една от колоните на извлечената таблица.
Това е почти всичко по отношение на резултатите от подзаявките. Но преди да продължим, може би сте забелязали, че логиката зад примера за множество стойности и други също може да се направи с помощта на JOIN.
-- Output multiple values which is a list of customers with lastnames that start with 'I'
USE [AdventureWorks]
GO
SELECT o.[SalesOrderID], o.[OrderDate], o.[ShipDate], o.[CustomerID]
FROM Sales.SalesOrderHeader o
INNER JOIN Sales.Customer c on o.CustomerID = c.CustomerID
INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID
WHERE p.LastName LIKE N'I%' AND p.PersonType = 'SC'Всъщност изходът ще бъде същият. Но кой се представя по-добре?
Преди да навлезем в това, позволете ми да ви кажа, че съм посветил раздел на тази гореща тема. Ще го разгледаме с пълни планове за изпълнение и ще разгледаме илюстрации.
Така че, изтърпете ме за момент. Нека обсъдим друг начин за поставяне на вашите подзаявки.
Други изявления, където можете да използвате SQL подзаявки
Досега използвахме SQL подзаявки за изрази SELECT. И работата е там, че можете да се насладите на предимствата на подзаявките за оператори INSERT, UPDATE и DELETE или във всеки T-SQL оператор, който формира израз.
Така че, нека да разгледаме серия от още няколко примера.
Използване на SQL подзаявки в оператори UPDATE
Достатъчно лесно е да включите подзаявки в операторите UPDATE. Защо не проверите този пример?
-- In the products inventory, transfer all products of Vendor 1602 to ----
-- location 6
USE [AdventureWorks]
GO
UPDATE [Production].[ProductInventory]
SET LocationID = 6
WHERE ProductID IN
(SELECT ProductID
FROM Purchasing.ProductVendor
WHERE BusinessEntityID = 1602)
GOВидяхте ли какво направихме там?
Работата е там, че можете да поставите подзаявки в клаузата WHERE на оператор UPDATE.
Тъй като го нямаме в примера, можете също да използвате подзаявка за клаузата SET като SET колона = ( подзаявка). Но бъдете предупредени: трябва да изведе една стойност, защото в противен случай възниква грешка.
Какво ще правим след това?
Използване на SQL подзаявки в изрази INSERT
Както вече знаете, можете да вмъквате записи в таблица с помощта на оператор SELECT. Сигурен съм, че имате представа каква ще бъде структурата на подзаявката, но нека демонстрираме това с пример:
-- Impose a salary increase for all employees in DepartmentID 6
-- (Research and Development) by 10 (dollars, I think)
-- effective June 1, 2020
USE [AdventureWorks]
GO
INSERT INTO [HumanResources].[EmployeePayHistory]
([BusinessEntityID]
,[RateChangeDate]
,[Rate]
,[PayFrequency]
,[ModifiedDate])
SELECT
a.BusinessEntityID
,'06/01/2020' as RateChangeDate
,(SELECT MAX(b.Rate) FROM [HumanResources].[EmployeePayHistory] b
WHERE a.BusinessEntityID = b.BusinessEntityID) + 10 as NewRate
,2 as PayFrequency
,getdate() as ModifiedDate
FROM [HumanResources].[EmployeeDepartmentHistory] a
WHERE a.DepartmentID = 6
and StartDate = (SELECT MAX(c.StartDate)
FROM HumanResources.EmployeeDepartmentHistory c
WHERE c.BusinessEntityID = a.BusinessEntityID)И така, какво гледаме тук?
- Първата подзаявка извлича последната ставка на заплатата на служител, преди да добави допълнителните 10.
- Втората подзаявка получава последния запис на заплатата на служителя.
- Накрая резултатът от SELECT се вмъква в таблицата EmployeePayHistory.
В други T-SQL изрази
Освен SELECT, INSERT, UPDATE и DELETE, можете също да използвате SQL подзаявки в следното:
Декларации на променливи или оператори SET в съхранени процедури и функции
Позволете ми да поясня с помощта на този пример:
DECLARE @maxTransId int = (SELECT MAX(TransactionID)
FROM Production.TransactionHistory)Като алтернатива можете да направите това по следния начин:
DECLARE @maxTransId intSET @maxTransId = (SELECT MAX(TransactionID) FROM Production.TransactionHistory)
В условни изрази
Защо не надникнете в този пример:
IF EXISTS(SELECT [Name] FROM sys.tables where [Name] = 'MyVendors')
BEGIN
DROP TABLE MyVendors
ENDОсвен това можем да го направим така:
IF (SELECT count(*) FROM MyVendors) > 0
BEGIN
-- insert code here
ENDНаправете SQL подзаявки със сравнение или логически оператори
Досега видяхме оператора равно (=) и оператора IN. Но има още много за изследване.
Използване на оператори за сравнение
Когато оператор за сравнение като =, ‹, ›, ‹›, ›= или ‹= се използва с подзаявка, подзаявката трябва да върне една стойност. Освен това възниква грешка, ако подзаявката върне множество стойности.
Примерът по-долу ще генерира грешка по време на изпълнение.
USE [AdventureWorks]
GO
SELECT b.LastName, b.FirstName, b.MiddleName, a.JobTitle, a.BusinessEntityID
FROM HumanResources.Employee a
INNER JOIN Person.Person b on a.BusinessEntityID = b.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory c on a.BusinessEntityID
= c.BusinessEntityID
WHERE c.DepartmentID = 6
and StartDate = (SELECT d.StartDate
FROM HumanResources.EmployeeDepartmentHistory d
WHERE d.BusinessEntityID = a.BusinessEntityID)Знаете ли какво не е наред в горния код?
На първо място, кодът използва оператора за равенство (=) с подзаявката. Освен това подзаявката връща списък с начални дати.
За да коригирате проблема, накарайте подзаявката да използва функция като MAX() в колоната за начална дата, за да върне една стойност.
Използване на логически оператори
Използване на EXISTS или NOT EXISTS
EXISTS връща TRUE, ако подзаявката върне редове. В противен случай връща FALSE. Междувременно използването на NOT EXISTS ще върне TRUE, ако няма редове, и FALSE, в противен случай.
Разгледайте примера по-долу:
IF EXISTS(SELECT name FROM sys.tables where name = 'Token')
BEGIN
DROP TABLE Token
ENDПърво, позволете ми да обясня. Кодът по-горе ще премахне маркера на таблицата, ако бъде намерен в sys.tables, което означава, че съществува в базата данни. Друг момент: препратката към името на колоната е без значение.
Защо така?
Оказва се, че машината на базата данни трябва само да получи поне 1 ред с помощта на EXISTS. В нашия пример, ако подзаявката върне ред, таблицата ще бъде премахната. От друга страна, ако подзаявката не е върнала нито един ред, следващите оператори няма да бъдат изпълнени.
По този начин проблемът на EXISTS е само редове и никакви колони.
Освен това EXISTS използва логика с две стойности: TRUE или FALSE. Няма случаи, в които ще върне NULL. Същото се случва, когато отхвърлите EXISTS с помощта на NOT.
Използване на IN или NOT IN
Подзаявка, въведена с IN или NOT IN, ще върне списък с нула или повече стойности. И за разлика от EXISTS, се изисква валидна колона с подходящия тип данни.
Нека изясня това с друг пример:
-- From the product inventory, extract the products that are available
-- (Quantity >0)
-- except for products from Vendor 1676, and introduce a price cut for the --- whole month of June 2020.
-- Insert the results in product price history.
USE [AdventureWorks]
GO
INSERT INTO [Production].[ProductListPriceHistory]
([ProductID]
,[StartDate]
,[EndDate]
,[ListPrice]
,[ModifiedDate])
SELECT
a.ProductID
,'06/01/2020' as StartDate
,'06/30/2020' as EndDate
,a.ListPrice - 2 as ReducedListPrice
,getdate() as ModifiedDate
FROM [Production].[ProductListPriceHistory] a
WHERE a.StartDate = (SELECT MAX(StartDate)
FROM Production.ProductListPriceHistory
WHERE ProductID = a.ProductID)
AND a.ProductID IN (SELECT ProductID
FROM Production.ProductInventory
WHERE Quantity > 0)
AND a.ProductID NOT IN (SELECT ProductID
FROM [Purchasing].[ProductVendor]
WHERE BusinessEntityID = 1676Както можете да видите от горния код, въведени са операторите IN и NOT IN. И в двата случая ще бъдат върнати редове. Всеки ред във външната заявка ще бъде съпоставен с резултата от всяка подзаявка, за да се получи продукт, който е наличен, и продукт, който не е от доставчик 1676.
Влагане на SQL подзаявки
Можете да влагате подзаявки дори до 32 нива. Независимо от това, тази възможност зависи от наличната памет на сървъра и сложността на другите изрази в заявката.
Какво мислите за това?
Според моя опит не си спомням влагане до 4. Рядко използвам 2 или 3 нива. Но това съм само аз и моите изисквания.
Какво ще кажете за добър пример, за да разберете това:
-- List down the names of employees who are also customers.
USE [AdventureWorks]
GO
SELECT
LastName
,FirstName
,MiddleName
FROM Person.Person
WHERE BusinessEntityID IN (SELECT BusinessEntityID
FROM Sales.Customer
WHERE BusinessEntityID IN
(SELECT BusinessEntityID
FROM HumanResources.Employee))Както можем да видим в този пример, влагането достигна 2 нива.
Лоши ли са SQL подзаявките за производителността?
С две думи: да и не. С други думи, зависи.
И не забравяйте, това е в контекста на SQL Server.
Като за начало, много T-SQL оператори, които използват подзаявки, могат алтернативно да бъдат пренаписани с помощта на JOINs. И производителността и за двете обикновено е еднаква. Въпреки това има конкретни случаи, когато присъединяването е по-бързо. И има случаи, когато подзаявката работи по-бързо.
Пример 1
Нека разгледаме пример за подзаявка. Преди да ги изпълните, натиснете Control-M или активирайте Включване на действителен план за изпълнение от лентата с инструменти на SQL Server Management Studio.
USE [AdventureWorks]
GO
SELECT Name
FROM Production.Product
WHERE ListPrice = SELECT ListPrice
FROM Production.Product
WHERE Name = 'Touring End Caps')Алтернативно, заявката по-горе може да бъде пренаписана с помощта на съединение, което дава същия резултат.
USE [AdventureWorks]
GO
SELECT Prd1.Name
FROM Production.Product AS Prd1
INNER JOIN Production.Product AS Prd2 ON (Prd1.ListPrice = Prd2.ListPrice)
WHERE Prd2.Name = 'Touring End Caps'В крайна сметка резултатът и за двете заявки е 200 реда.
Освен това можете да проверите плана за изпълнение и за двата отчета.
Какво мислиш? Практически еднакви ли са? С изключение на действителното изминало време на всеки възел, всичко останало е основно същото.
Но ето още един начин да го сравним, освен визуалните разлики. Предлагам да използвате Сравнение на плана за показване.
За да го изпълните, изпълнете следните стъпки:
- Щракнете с десния бутон върху плана за изпълнение на израза, като използвате подзаявката.
- Изберете Запазване на плана за изпълнение като.
- Именувайте файла subquery-execution-plan.sqlplan.
- Отидете до плана за изпълнение на израза с помощта на съединение и щракнете с десния бутон върху него.
- Изберете Сравнете Showplan.
- Изберете името на файла, който сте записали в #3.
Сега проверете това за повече информация относно Сравнете Showplan.
Трябва да можете да видите нещо подобно на това:
Забележете приликите:
- Прогнозните редове и разходи са същите.
- QueryPlanHash също е един и същ, което означава, че имат подобни планове за изпълнение.
Все пак обърнете внимание на разликите:
- Размерът на плана на кеша е по-голям при използване на присъединяването, отколкото при използване на подзаявката
- Компилирайте CPU и времето (в ms), включително паметта в KB, използвано за анализиране, обвързване и оптимизиране на плана за изпълнение, е по-високо при използване на присъединяването, отколкото при използване на подзаявката
- Процесорното време и изминалото време (в ms) за изпълнение на плана са малко по-високи при използване на присъединяването спрямо подзаявката
В този пример подзаявката е малко по-бърза от присъединяването, въпреки че получените редове са еднакви.
Пример 2
В предишния пример използвахме само една таблица. В примера, който следва, ще използваме 3 различни таблици.
Нека направим това да се случи:
-- Subquery example USE [AdventureWorks] GO SELECT [SalesOrderID], [OrderDate], [ShipDate], [CustomerID] FROM Sales.SalesOrderHeader WHERE [CustomerID] IN (SELECT c.[CustomerID] FROM Sales.Customer c INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID WHERE p.PersonType='SC')-- Join example USE [AdventureWorks] GO SELECT o.[SalesOrderID], o.[OrderDate], o.[ShipDate], o.[CustomerID] FROM Sales.SalesOrderHeader o INNER JOIN Sales.Customer c on o.CustomerID = c.CustomerID INNER JOIN Person.Person p ON c.PersonID = p.BusinessEntityID WHERE p.PersonType = 'SC'
И двете заявки извеждат едни и същи 3806 реда.
След това нека да разгледаме техните планове за изпълнение:
Можете ли да видите двата плана за изпълнение и да намерите разлика между тях? На пръв поглед изглеждат еднакви.
Но по-внимателното изследване с Сравнение на плана за показване разкрива какво наистина има вътре.
Нека започнем, като анализираме няколко прилики:
- Розовото осветяване в плана за изпълнение разкрива подобни операции и за двете заявки. Тъй като вътрешната заявка използва съединение вместо вложени подзаявки, това е съвсем разбираемо.
- Очакваните разходи за оператор и поддърво са еднакви.
След това нека да разгледаме разликите:
- Първо, компилацията отне повече време, когато използвахме обединения. Можете да проверите това в Compile CPU и Compile Time. Заявката с подзаявка обаче отне по-висока памет за компилиране в KB.
- Тогава QueryPlanHash на двете заявки е различен, което означава, че имат различен план за изпълнение.
- И накрая, изминалото време и процесорното време за изпълнение на плана са по-бързи при използване на присъединяване отколкото при използване на подзаявка.
Ефективност на подзаявка срещу присъединяване
Вероятно ще се сблъскате с твърде много други проблеми, свързани със заявка, които могат да бъдат разрешени чрез използване на присъединяване или подзаявка.
Но най-важното е, че подзаявката не е присъщо лоша в сравнение с присъединяването. И няма основно правило, че в конкретна ситуация съединението е по-добро от подзаявка или обратното.
Така че, за да сте сигурни, че имате най-добрия избор, проверете плановете за изпълнение. Целта на това е да се разбере как SQL Server ще обработи конкретна заявка.
Въпреки това, ако решите да използвате подзаявка, имайте предвид, че може да възникнат проблеми, които ще тестват вашите умения.
Често срещани предупреждения при използване на SQL подзаявки
Има 2 често срещани проблема, които могат да накарат вашите заявки да се държат диво, когато използвате SQL подзаявки.
Болката от разрешаването на имена на колони
Този проблем въвежда логически грешки във вашите заявки и те може да са много трудни за намиране. Един пример може допълнително да изясни този проблем.
Нека започнем, като създадем таблица за демонстрационни цели и я попълним с данни.
USE [AdventureWorks]
GO
-- Create the table for our demonstration based on Vendors
CREATE TABLE Purchasing.MyVendors
(
BusinessEntity_id int,
AccountNumber nvarchar(15),
Name nvarchar(50)
)
GO
-- Populate some data to our new table
INSERT INTO Purchasing.MyVendors
SELECT BusinessEntityID, AccountNumber, Name
FROM Purchasing.Vendor
WHERE BusinessEntityID IN (SELECT BusinessEntityID
FROM Purchasing.ProductVendor)
AND BusinessEntityID like '14%'
GOСега, когато таблицата е настроена, нека стартираме някои подзаявки, използвайки я. Но преди да изпълните заявката по-долу, не забравяйте, че идентификаторите на доставчици, които използвахме от предишния код, започват с „14“.
SELECT b.Name, b.ListPrice, a.BusinessEntityID
FROM Purchasing.ProductVendor a
INNER JOIN Production.Product b on a.ProductID = b.ProductID
WHERE a.BusinessEntityID IN (SELECT BusinessEntityID
FROM Purchasing.MyVendors)Горният код работи без грешки, както можете да видите по-долу. Както и да е, обърнете внимание на списъка с BusinessEntityIDs.
Не вмъкнахме ли данни с BusinessEntityID, започващи с „14“? Тогава какво има? Всъщност можем да видим BusinessEntityIDs, които започват с „15“ и „16“. Откъде се взеха тези?
Всъщност заявката изброява всички данни от таблицата ProductVendor.
В такъв случай може да си помислите, че псевдонимът ще разреши този проблем, така че да препраща към таблицата MyVendors точно като тази по-долу:
Освен че сега истинският проблем се появи поради грешка по време на изпълнение.
Проверете отново таблицата MyVendors и ще видите, че вместо BusinessEntityID, името на колоната трябва да бъде BusinessEntity_id (с долна черта).
По този начин използването на правилното име на колона най-накрая ще реши този проблем, както можете да видите по-долу:
Както можете да видите по-горе, сега можем да наблюдаваме BusinessEntityIDs, започващи с „14“, точно както очаквахме преди.
Но може да се чудите: защо, за бога, SQL Server позволи успешно изпълнение на заявката на първо място?
Ето го главната идея: Резолюцията на имена на колони без псевдоним работи в контекста на подзаявката от самото излизане към външната заявка. Ето защо препратката към BusinessEntityID в подзаявката не задейства грешка, защото се намира извън подзаявката — в ProductVendor маса.
С други думи, SQL Server търси колоната без псевдоним BusinessEntityID в таблицата MyVendors. Тъй като го няма, той погледна навън и го намери в таблицата ProductVendor. Лудост, нали?
Може да кажете, че това е грешка в SQL Server, но всъщност тя е замислена в стандарта SQL и Microsoft го последва.
Добре, това е ясно, не можем да направим нищо за стандарта, но как можем да избегнем грешка?
- Първо, добавете префикс към имената на колоните с името на таблицата или използвайте псевдоним. С други думи, избягвайте имена на таблици без префикс или псевдоними.
- Второ, имайте последователно именуване на колоните. Избягвайте да имате едновременно BusinessEntityID и BusinessEntity_id, например.
Звучи добре? Да, това внася малко разум в ситуацията.
Но това не е краят.
Луди NULLs
Както споменах, има още за покриване. T-SQL използва 3-стойностна логика поради поддръжката си за NULL. И NULL може почти да ни подлуди, когато използваме SQL подзаявки с NOT IN.
Позволете ми да започна с представянето на този пример:
SELECT b.Name, b.ListPrice, a.BusinessEntityID
FROM Purchasing.ProductVendor a
INNER JOIN Production.Product b on a.ProductID = b.ProductID
WHERE a.BusinessEntityID NOT IN (SELECT c.BusinessEntity_id
FROM Purchasing.MyVendors c)Резултатът от заявката ни води до списък с продукти, а не в таблицата MyVendors, както се вижда по-долу:
Сега да предположим, че някой неволно е вмъкнал запис в таблицата MyVendors с NULL BusinessEntity_id. Какво ще правим по въпроса?
Къде отидоха всички данни?
Виждате ли, операторът NOT отрича предиката IN. Така че NOT TRUE сега ще стане FALSE. Но NOT NULL е НЕИЗВЕСТНО. Това накара филтъра да отхвърли редовете, които са НЕИЗВЕСТНИ, и това е виновникът.
За да сте сигурни, че това няма да ви се случи:
- Или накарайте колоната на таблицата да забранява NULL, ако данните не трябва да са по този начин.
- Или добавете името на колона IS NOT NULL към вашата клауза WHERE. В нашия случай подзаявката е както следва:
SELECT b.Name, b.ListPrice, a.BusinessEntityID
FROM Purchasing.ProductVendor a
INNER JOIN Production.Product b on a.ProductID = b.ProductID
WHERE a.BusinessEntityID NOT IN (SELECT c.BusinessEntity_id
FROM Purchasing.MyVendors c
WHERE c.BusinessEntity_id IS NOT NULL)Вижте основните изводи на тази статия и се запознайте с допълнителни препратки за вашето удоволствие от четенето тук — https://codingsight.com/the-easy-guide-on-how-to-use-subqueries-in-sql-server/ .
Присъединете се към общността на CodingSight и се абонирайте за нашия дайджест!
