Introduction To SQL Programming

Part-4

SQL Join Function

JOIN clause is used to combine rows from two or more tables, based on a related column between them. Recall the school play incident we discussed earlier. We will now see how it can be implemented.

create database studentlist;
create table Aron(name varchar(100),section varchar(100),primary key(name));
insert into Aron (name,section) values ("Aman","A");
insert into Aron (name,section) values ("Varun","B");
insert into Aron (name,section) values ("Nikhil","A");
insert into Aron (name,section) values ("Naman","C");
create table Arun(name varchar(100),hours int,primary key(name));
insert into Arun (name,hours) values ("Aman","9");
insert into Arun (name,hours) values ("Varun","6");
insert into Arun (name,hours) values ("Nikhil","4");
insert into Arun (name,hours) values ("Naman","7");
select "Here is the combined table for our awesome ma'am!";
select Aron.name,Aron.section,Arun.hours from Aron LEFT JOIN Arun on Aron.name=Arun.name;

Types Of Table Join

Inner Join

The inner join is used to select all matching rows or columns in both tables or as long as the defined condition is valid in SQL.

Select column_1, column_2, column_3 FROM table_1 INNER JOIN table_2 ON table_1.column = table_2.column;  

Left Join

The LEFT JOIN is used to retrieve all records from the left table (table1) and the matched rows or columns from the right table (table2). If both tables do not contain any matched rows or columns, it returns the NULL.

Select column_1, column_2, column(s) FROM table_1 LEFT JOIN table_2 ON table_1.column_name = table_2.column_na

Right Join

The RIGHT JOIN is used to retrieve all records from the right table (table2) and the matched rows or columns from the left table (table1). If both tables do not contain any matched rows or columns, it returns the NULL.

Select column_1, column_2, column(s) FROM table_1 RIGHT JOIN table_2 ON table_1.column_name = table_2.column_name;  

Self Join

A self join is a regular join, but the table is joined with itself.

Select column1, column2, column(s) FROM table_1 Tbl1, table_2 Tbl2 WHERE condition; 

Full Join

It is a combination result set of both LEFT JOIN and RIGHT JOIN. The joined tables return all records from both the tables and if no matches are found in the table, it places NULL. It is also called a FULL OUTER JOIN.

Select column_1, column_2, column(s) FROM table_1 FULL JOIN table_2 ON table_1.column_name = table_2.column_na

Cross Join

It is also known as CARTESIAN JOIN, which returns the Cartesian product of two or more joined tables. The CROSS JOIN produces a table that merges each row from the first table with each second table row.

Select * from table_1 cross join table_2;  

Aliases

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable and it only exists for the duration of that query. An alias is created with the AS keyword.

create database barca;
create table team(jerseynumber integer not null, name varchar(100));
insert into team(jerseynumber,name) values (3,"Pique");
insert into team(jerseynumber,name) values (7,"Griezman");
insert into team(jerseynumber,name) values (18,"Alba");
insert into team(jerseynumber,name) values (8,"Pjanic");
insert into team(jerseynumber,name) values (12,"Puig");
insert into team(jerseynumber,name) values (16,"Pedri");
insert into team(jerseynumber,name) values (10,"Messi");
SELECT jerseynumber as unusualjerseynumber from team where (jerseynumber>10);