Inner Join

Inner Join is a join that produces minimal rows that there is a rows in both tables that match the join condition, which is different rows with another row in a table will be removed. In the SQL-92 standard, inner join is expressed in FROM or WHERE clause. Inner join is the only joint which is supported by SQL-92 standard in the WHERE clause. Inner join in WHERE clause otherwise known as old style inner-join.

In the examples in this module we will use an existing database. For example we will use data from the pubs database to display the Title column from Titles table and column PubName from Publishers table. Both these tables have a relationship at Pub_id column.

SELECT t.Title, p.Pub_Name FROM Publishers p INNER JOIN Titles t
ON p.Pub_id = t.Pub_id ORDER BY Title ASC

If you use multiple joins to connect more than two tables, it's easier if you think about each joint separately. For example you have three tables: A, B and C will be connected. First join will linking A and B, and then the results will be combined with C with the second join to produce the final data. Query optimizer will make a plan of execution, although the result is same. From a number of plans selected the most optimal execution.

How to Use CREATE DATABASE Command

The command "CREATE DATABASE" can be used to create a database and files to store data. You can also express some of the parameters that define the characteristics of a database. For example you can declare a maximum size of the file. If you use a simple command is:

CREATE DATABASE database_name

without parameters, the database will be created with the same size with a database that was created by  SQL Server named Model. SQL Server implemented CREATE DATABASE command in two phases:

1. SQL Server, copying the Model database for the initial database with meta data.
2. Then, SQL Server fills the page blank except for page that contains internal data that declare how to use a place name in the database. 

All objects in the database Model will be copied into the new database. You can also add objects such as tables, views, stored procedures, data types, etc into the database Model, so every time you create a new database objects will be included.

Each of the settings of the database model will be included in the new database unless you use the FOR ATTACH. For example the Model database option select into / bulkcopy set to OFF. If you change the database option with the command ALTER DATABASE model, the setting will affect the new database will be created.

Using UNION operator

You can combine the results of several queries with UNION operator. By default, duplicate rows will be removed. To maintain a duplicate, then use the keyword ALL. Some rules of the UNION operator :

• Queries will be merged, must have many same column,same order, and similar data types.
• If you use INTO clause in one query, you should put that query as first query.
• You can use GROUP BY and HAVING clause in individual query.
• ORDER BY clause and compute, are permitted only at the end of the UNION operator, to define the sequence of final results or to calculate the final value.

 For example we will display all of the customers and suppliers from the USA, sorted by CompanyName:

SELECT CompanyName, ContactName FROM
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

If you want to save the results combined into a temporary table, you must use the INTO clause followed by table name, for example TempUSA

SELECT CompanyName, ContactName FROM
INTO TempUSA
Suppliers WHERE Country = ‘USA’
UNION
SELECT CompanyName, ContactName
FROM Customers
WHERE Country=’USA’ ORDER BY CompanyName

Subquery With EXISTS Operator

If a subquery uses EXISTS, such as testing the same function exists or not. WHERE clause test the existence of the outer query-lines produced by the subquery. Subquery does not return data, but produces the value TRUE or FALSE. Subquery examples that use IN, will be changed by using EXISTS:

SELECT DISTICT FompanyName,PostalCode FROM Customers WHERE EXITS (SELECT * FROM Suppliers WHERE PostalCode = Customers.PostalCode)

If you use exists, it must use SELECT *. Keyword, NOT EXISTS is the opposite of EXISTS, WHERE clause subquery is used to indicate that there is no result.

Compute and Compute BY clause

In the previous article I had explained about "order by" clause, now I will explain about the compute and compute by clause.COMPUTE and COMPUTE BY clause is used to generate new rows of data containing detailed and summary. COMPUTE  clause produces detail lines and a line that contains the summary. For example we will display all the data from Titles table from Pubs database, this is the same as ordinary SELECT command. After the last line of the data, we want to display the number of fields ytd_sales, for that we need the COMPUTE clause.

Here is the Example:

SELECT * FROM Titles compute sum(ytd_sales)

COMPUTE BY clause  producing new lines of summary data, similar to the GROUP BY clause, but the result rows as a subgroup with the values summarized. If you use COMPUTE BY clause must be accompanied with ORDER BY. For example we want to display data from table Titles sorted by Type, and after  Type, a total of ytd_sales displayed for each Type, then the command is:

SELECT type,ytd_sales FROM Titles ORDER BY type compute sum(ytd_sales) by type

Learning About ORDER BY clause

ORDER BY clause used to sort the results based on one or more columns. Ordering can be ascending (ASC) or descending (DESC), the default is ASC. If sorting by several columns, nested sequencing process.

For example we will display the employee data was sorted by country:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country

Data will be sorted based on the Country. Notice, how the LastName, LastName whether in each country will be sorted? No, if we wish for a country such data was sorted by LastName, you must declare two fields in the ORDER BY:

SELECT country, EmployeeID, LastName, FirstName FROM Employees ORDER BY Country, LastName

Adding Data With Subquery

You can use subquery, SELECT in INSERT statement to add values to a table or several tables or views. Subquery allows you to add a few rows at once into a table.

What is the difference subquery usage, with usage VALUES?. Subquery will add a number of existing data, while the VALUES to add new data. For example, we create a new table named TmpPublisher into the pubs database. The table contains four fields and made with command :

CREATE TABLE TmpPublisher
SELECT pub_name varchar, city, state, country FROM Publisher WHERE COUNTRY =’USA’

If the subquery is used in INSERT command, then the subquery is not necessary are enclosed in brackets. Subquery produce the same four columns and fields in the destination table. For example in tanel pub_name TmpPublisher will be filled only for the data not from the USA, then the INSERT clause you should declare a column that will contain the data.

INSERT INTO TmpPublisher (pub_name)
 SELECT pub_name FROM Publisher WHERE NOT COUNTRY = 'USA'