Lesson 1

In the last module, you learned about the basics of queries. You learned how to execute your queries, and how to incorporate logic, system catalogs, and more. This module dives into selecting data from your databases. What would be the point of having data in your database if you can not get it out? This module is going to teach you how to do that.

• Learning Objectives
  After completing this module, you will be able to:
  1. Construct inner and outer joins
  2. Alias your table and column names
  3. Construct union queries
  4. Write subqueries and understand when they are used
  5. Write aggregated queries
  6. Write union queries
  7. Apply additional keywords that can be used in your queries
  8. Write distributed select statements

In SQL (Structured Query Language), JOIN is used to combine rows from two or more tables based on a related column between them. There are several types of JOIN operations in SQL, including INNER JOIN and OUTER JOIN. These operations allow you to manipulate and combine data in complex ways.
INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables. An INNER JOIN operation retrieves only the matching rows from both the tables.
Let's say we have two tables, Orders and Customers.
The Orders table is as follows:

OrderID	CustomerID	Product
1	3	Apples
2	1	Bananas
3	2	Grapes
4	4	Oranges

And the Customers table:

CustomerID	CustomerName
1	John Doe
2	Jane Doe
3	Mary Johnson

You can join these tables using an INNER JOIN as follows:

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

The result would be:

OrderID	CustomerName	Product
1	Mary Johnson	Apples
2	John Doe	Bananas
3	Jane Doe	Grapes

Note that the Order with OrderID 4 is not in the result, because the CustomerID 4 from the Orders table does not have a matching record in the Customers table.

The OUTER JOIN keyword returns all records when there is a match in either the left (table mentioned before the JOIN keyword) or right table (table mentioned after the JOIN keyword), and null when there is no match. An OUTER JOIN can be further classified into LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. Let's continue with the above example and perform a LEFT OUTER JOIN:

SELECT Orders.OrderID, Customers.CustomerName, Orders.Product
FROM Orders
LEFT OUTER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

The result would be:

OrderID	CustomerName	Product
1	Mary Johnson	Apples
2	John Doe	Bananas
3	Jane Doe	Grapes
4	NULL	Oranges

This result includes all the records from the Orders table (left table), and matching records from the Customers table (right table). When there is no match, the result is NULL. That's a basic overview of INNER JOIN and OUTER JOIN in SQL. Practice is key to understanding these concepts more deeply, so try using them with a variety of different tables and datasets.

(UDFs) User-defined functions have a tremendous number of similarities to sprocs, except that they: Can return a value of most SQL Server data types. Excluded return types include text, ntext, image, cursor, and timestamp. Cannot have side effects and basically, they cannot do anything that reaches outside the scope of the function, such as changing tables, sending e-mails, or making system or database parameter changes. UDFs are similar to the functions that you would use in a standard programming language such as VB.NET or C++. You can pass more than one variable in and get a value out. SQL Server's UDFs vary from the functions found in many procedural languages, in that all variables (except table with passing in variables By Ref or passing in pointers, sorry, there is no equivalent here. There is, however, some good news in that you can return a special data type called a table.
In the next lesson, you will get an overview of the SELECT statement, which is used to retrieve data from your tables.