SQL Joins

Full Outer Join

Inner Join

Left Join

Right Join

Left Excluding Join

Right Excluding Join

Many to Many Join

Stored Procedures

Stored procedures are database objects that group one or more T-SQL statements.

Stored procedures are used to group SQL commands that form a single, logical action. For example, let’s say that we want to add to our website functionality that allows departments to be deleted. However, as we know, we must delete all the department’s employees before we can delete the department itself. To help with such management issues, we could have a stored procedure that copies the employees of that department to another table (called EmployeesBackup),
deletes those employees from the main Employees table, then removes the department from the Department table. As we can imagine, having all this logic saved as a stored procedure can make working with databases much easier.

The basic form of a stored procedure is as follows:

CREATE PROCEDURE ProcedureName
(
  @Parameter1 DataType,
  @Parameter2 DataType,
  ⋮ 
)
AS
  -- an optional comment
  ⋮ SQL Commands

The leading “--” marks a comment. The parameter names, as well as the names of
variables we can declare inside stored procedures, start with @. As we might expect,
their data types are the same data types supported by SQL Server.

The stored procedure shown below creates a new department whose name is specified through the first parameter. It then creates a new employee whose name is specified as the second parameter, assigns the new employee to the new department, and finally deletes both the new employee and the new department.

CREATE PROCEDURE DoThings
(
  @NewDepartmentName VARCHAR(50),
  @NewEmployeeName VARCHAR(50),
  @NewEmployeeUsername VARCHAR(50)
)
AS
-- Create a new department
INSERT INTO Departments (Department)
VALUES (@NewDepartmentName)
-- Obtain the ID of the created department
DECLARE @NewDepartmentID INT
SET @NewDepartmentID = scope_identity()
-- Create a new employee
INSERT INTO Employees (DepartmentID, Name, Username)
VALUES (@NewDepartmentID, @NewEmployeeName, @NewEmployeeUsername)
-- Obtain the ID of the created employee
DECLARE @NewEmployeeID INT
SET @NewEmployeeID = scope_identity()
-- List the departments together with their employees
SELECT Departments.Department, Employees.Name
FROM Departments
INNER JOIN Employees ON Departments.DepartmentID =
    Employees.DepartmentID
-- Delete the new employee
DELETE FROM Employees
WHERE EmployeeID = @NewEmployeeID
-- Delete the new department
DELETE FROM Departments
WHERE DepartmentID = @NewDepartmentID

Execute this code to have the DoThingsstored procedure saved to our database. We can now execute our new stored procedure by supplying the required parameters as follows:

EXECUTE DoThings 'Research', 'Cristian Darie', 'cristian'

If we execute the procedure multiple times, We’ll achieve the same results, since
any data that’s created as part of the stored procedure is deleted at the end of the
stored procedure:

(1 row(s) affected)
(1 row(s) affected)
Department                       Name
-------------------------------- --------------------------------
Executive                        Zak Ruvalcaba
Marketing                        Jessica Ruvalcaba
Engineering                      Ted Lindsey
Engineering                      Shane Weebe
Marketing                        David Levinson
Accounting                       Geoff Kim
Research                         Cristian Darie
(7 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)

SQL Subqueries vs Joins

In most cases Joins are faster than sub-queries and it is very rare for a sub-query to be faster.

In Joins RDBMS can create an execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing.

The good thing in sub-queries is that they are more readable than Joins, that's why most new SQL people prefer them; it is the easy way; but when it comes to performance, Joins are better in most cases even though they are not hard to read too.

The Subquery way: 

SELECT * FROM Persons WHERE (P_Id NOT IN(SELECT DISTINCT P_Id FROM orders))

The above subquery is equivalent to a left excluding Join: 

SELECT * FROM Persons 
LEFT JOIN Orders on Persons.P_Id = Orders.P_Id
WHERE Orders.P_Id IS NULL