–Joining two tables without a clause i.e, cross products for two tables
USE Pubs SELECT * from authors,titleauthor GO SELECT * from authors cross join titleauthor GO
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 ORDER BY PUBLISHER
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.