SQL-Ex blog

CREATE TABLE Instructor

(

	PersonId INT NOT NULL PRIMARY KEY,

	HireDate DATETIME

)

CREATE TABLE Student

(

	PersonId INT NOT NULL PRIMARY KEY,

	EnrollmentDate DATETIME

)

INSERT INTO Instructor (PersonId, HireDate)

SELECT Person.PersonID, Person.HireDate

FROM Person

WHERE Person.Discriminator = 'Instructor'

INSERT INTO Student (PersonId, EnrollmentDate)

SELECT Person.PersonID, Person.EnrollmentDate

FROM Person

WHERE Person.Discriminator = 'Student'

ALTER TABLE Person

DROP COLUMN EnrollmentDate, HireDate

Ничего особенного, просто перенес EnrollmentDate и HireDate в их собственные таблицы.

Примеры сделаны для этой базы. Скажем, я хочу выбрать все предлагаемые курсы (Courses) и вывести имя инструктора, если инструктор поступил на работу до 1 января 2000. Возвращаемый мне набор данных должен показать название курса и имя инструктора, если последний был принят на работу до 2000 года.

Существует множество способов сделать это, но давайте проделаем это вместе. Мы знаем, что нам необходимы название курса и имя лица, и нам нужно будет обратиться к таблице инструкторов за датой найма на работу, и что мы должны использовать некоторые левые соединения, поскольку мы всегда хотим получить название курса.

SELECT		Course.Title, Person.FirstName, Person.LastName, Instructor.HireDate

FROM		Course

LEFT JOIN	CourseInstructor

	   ON	CourseInstructor.CourseID = Course.CourseID

LEFT JOIN	Person

	   ON	Person.PersonID = CourseInstructor.PersonID

LEFT JOIN	Instructor

	   ON	Instructor.PersonId = Person.PersonID

Тут мы уже прошли большую часть пути. Теперь мы имеем все данные, но вопрос в том, как избежать возврата имени лица, если HireDate позже 1 января 2000.

Мы не можем просто разместить это условие в предложении WHERE, поскольку тогда не будет возвращаться название курса. Мы могли бы сделать это частью условия соединения с инструктором, но это ограничит только возвращаемые даты найма. Мы могли бы использовать это для построения оператора CASE над полями FirstName и LastName, а какие еще есть варианты?

Что мы хотим сделать, так это ограничить соединение с Person, чтобы строки возвращались только в случае возврата инструктора (Instructor), а затем могли бы наложить условие соединения с Instructor. Что-то типа этого, но оно не работает:

SELECT Course.Title, Person.FirstName, Person.LastName, Instructor.HireDate

FROM		Course

LEFT JOIN	CourseInstructor

	   ON	CourseInstructor.CourseID = Course.CourseID

LEFT JOIN	Person

	   ON	Person.PersonID = CourseInstructor.PersonID

	   AND	Instructor.PersonId IS NOT NULL

LEFT JOIN	Instructor

	   ON	Instructor.PersonId = Person.PersonID

	   AND	Instructor.HireDate < '1 Jan 2000'

Что-то подобное можно сделать множеством способов. Нам нужно выполнить внутреннее соединение Person и Instructor, не затрагивая левое соединение между CourseInstructor и Person. Оказывается, мы можем сказать SQL о том, что мы хотим, различными способами. Обычный способ - это просто соединить Person и Instructor в подзапросе, например:

SELECT		Course.Title, Person.FirstName, Person.LastName, Person.HireDate

FROM		Course

LEFT JOIN	CourseInstructor

	   ON	CourseInstructor.CourseID = Course.CourseID

LEFT JOIN	(

	SELECT	Person.*, Instructor.HireDate

	FROM	Person

	INNER JOIN	Instructor

		   ON	Instructor.PersonId = Person.PersonID

	WHERE Instructor.HireDate < '1 Jan 2000'

	) Person

ON	Person.PersonID = CourseInstructor.PersonID

И это определенно работает. Но прячет все столбцы, на которые вы захотели бы сослаться. Теперь, если я захочу получить HireDate, я должен присвоить алиас подзапросу. На самом деле в этом нет ничего страшного. Но есть другой способ? Да! Оказывается все, что нам нужно - это переместить предложение ON для Person, чтобы оно оказалось после соединения с Instructor. Посмотрите:

SELECT		Course.Title, Person.FirstName, Person.LastName, Instructor.HireDate

FROM		Course

LEFT JOIN	CourseInstructor

	   ON	CourseInstructor.CourseID = Course.CourseID

LEFT JOIN	Person

	INNER JOIN	Instructor

		   ON	Instructor.PersonId = Person.PersonID

		   AND	Instructor.HireDate < '1 Jan 2000'

ON	Person.PersonID = CourseInstructor.PersonID

Это даст вам тот же результат, что и подзапрос, и почти тот же план запроса (в ваш план должен добавиться compute scalar). SQL выполнит работу по построению и выполнению подзапроса вместо вас, поэтому вы можете получить чистый код. Я нахожу это надежным вариантом, который дает план того же вида, что и с подзапросом, и обычно выполняется так же. Я предпочитаю читабельность этого кода, так как выбираемые столбцы сразу говорят вам, откуда берутся данные. ОДНАКО это не то, как обычно пишется SQL. Я попросил рецензентов оценить правильность кода, другие разработчики и администраторы баз данных были озадачены тем, что он делает. Что касается меня, то мне это нравится, но, поскольку это слегка отличается от того, что обычно используют люди, вы можете придерживаться обычного шаблона с подзапросом.