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.
… 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.