Joins

sql-joins

Inner join – Selects the records that have matchng values in both tables

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

Full join – Selects all records that match either left or right table records

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Left Join – Selects records from the left (first) table with matching right table records

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Right Join – Selects records from the right (second) table with matching left table records.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Truncate vs delete

Truncate can be used to delete records, although as with drop this cannot be undone.

Pro’s and cons

  • Pro – Quicker as there’s less logging or lock safeguards
  • Con – cannot be undone
  • Con – you cannot use a where query, it’s all records or nothing
  • Pro – After truncating, you can re-use a deleted row’s id property, DELETE will carry on with the seed as if the record was still there

Unions

Union

Unions are used to combine the records from multiple select clauses together, each collection must have the same number of columns and similar data types and the columns must be in the same order.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

Union all

Combines the select statement results and returns only the unique records.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Partition

To do…

References:

Advertisements