SQL Server Basic – VII

–Joining two tables without a clause i.e, cross products for two tables

USE Pubs
SELECT * from authors,titleauthor
SELECT * from authors cross join titleauthor

This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table
and 30 records in the second we will get a total of 600 records.
Not putting a where clause is same as a CROSS JOIN.

–Using cross products to generate unique derived fields

USE Pubs
SELECT distinct (stor_id + '-' + au_lname) compfld
from stores cross join authors
order by compfld

‘+’ operators concatenates two fields for composite field generation Primary key of two different tables can be used to create
a composite key in a link table this way.

–Using Inner Joins to find exact matching rows

SELECT au_id,pubdate
from titleauthor inner join titles
ON titleauthor.title_id = titles.title_id

INNER JOIN exactly matches the common key on either sides of the Join

–Conventional Inner Joins

SELECT au_id,pubdate
from titleauthor, titles
WHERE titleauthor.title_id = titles.title_id

–Inner Joins having more than two tables

SELECT   au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]
from       titleauthor
INNER    JOIN titles
ON          titleauthor.title_id = titles.title_id
JOIN      publishers
ON          publishers.pub_id = titles.pub_id

INNER JOIN is the default type of Join. Specifying it is not always needed

–Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],
Convert(char(12),pubdate) [PUBLISHED ON]
from titleauthor
JOIN titles
ON titleauthor.title_id = titles.title_id
JOIN publishers
ON publishers.pub_id = titles.pub_id
WHERE pub_name like 'ALGO%'
AND au_id >= '3%'

Multiple Inner Joins further refined by a Where / And Clause Convert function gives the date in a more friendly format.
Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)
‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

–Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titles a
left join titleauthor b
ON a.title_id = b.title_id
order by a.title,b.au_ord

Left Join brings all the rows from table appearing first in the JOIN clause

–Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id
from titleauthor b 
right join titles a
ON a.title_id = b.title_id
order by a.title,b.au_ord

Right Join brings all the rows from table appearing second in the JOIN clause.

–LeftJoin in two tables having a filter

select                    stor_name,CAST(discount AS VARCHAR) + '%' '%'
from                       discounts
LEFT JOIN              stores
ON                          discounts.stor_id = stores.stor_id
WHERE                   discounts.stor_id IS NOT NULL

The results can be filtered using a trailing Where.
Left join brings all the records from discounts & nulls for unrelated stores records CAST does the same jobs as convert but lacks formatting options Casting needed as % is a character.
Here first ‘%’ becomes part of the data & the second becomes header of the column.