My Notes – SQL

This Transact-SQL query is an example of an inner join:

USE pubs
SELECT *
FROM authors AS a INNER JOIN publishers AS p
   ON a.city = p.city
ORDER BY a.au_lname DESC

This inner join is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.

======

Sub Queries That Return No Rows

… When creating tables, it can be very useful to write the SQL to use a sub query which will not return any rows – when just the table structure is required and not any of the data.

In the following example we create a copy of the policy table with no rows:

CREATE TABLE new_policy AS
(SELECT * from policy WHERE FALSE=TRUE);

The sub query returns no data but does return the column names and data types to the ‘create table‘ statement.