JOIN:-we use join to select the data from one or more table based on the common value or field between them.ii is used to maintain relation between 2 tables.
tables on which we perform join.in both table we have at least one same column name.here we have id column present in both table we select data on the basis of id column.
table 1:-table name-class
table 2:-table name-sec
table 3:-table name -main
INNER JOIN:-it displays the data from both tables which have same column value.
retrieve all row which have matching value in both table.
Ex:-here we compare id column of class table with sec table it return only those row which have equal id from both table.
Ex:- if id column of class table does not match with id column of sec table then it returns nothing or empty table.for output there must be at least single row which matches in both table.like this no matching row in both table return empty table.
LEFT JOIN:-it returns all the rows from left table and the matching rows from the right table and those row which does not match at that place it returns null value.
Ex:-here it returns all the row of class table and the matching row of right table.in last row the column does not match with right table column so it returns null value.
it returns all the rows of left table even single row does not math with right table.
take another table look like this
table name:-main
Ex:-we perform operation on class table and main table.no matches with right table it returns all row of left table and null value for right table.
RIGHT JOIN:-opposite of left join.it returns all the rows of right table and matching column of left table and returns null if the values did not matches.
Ex:-here we perform operation on class and sec table.
it returns all the row of right table and matching row of left table it compares on the basis of id column
if left table column not matches then it return null value.
it return all the row of right table even if there is no single match between both table.
Ex:-we perform operation on class table and main table.
it returns all the rows of right table and there is no single matches in left table so it return null in left.
FULL OUTER JOIN:-it returns all the rows of both table even if there is no match between the table.
if there is no match then it returns null as a value.
Ex:-we perform operation on class and sec table.
it return all row from both table if id is not match then it return null.
if there is no single match between both table then it returns all the rows of both table.
Ex:-we perform operation on class table and main table.it returns all the rows of both table.
tables on which we perform join.in both table we have at least one same column name.here we have id column present in both table we select data on the basis of id column.
table 1:-table name-class
INNER JOIN:-it displays the data from both tables which have same column value.
retrieve all row which have matching value in both table.
Ex:-here we compare id column of class table with sec table it return only those row which have equal id from both table.
Ex:- if id column of class table does not match with id column of sec table then it returns nothing or empty table.for output there must be at least single row which matches in both table.like this no matching row in both table return empty table.
LEFT JOIN:-it returns all the rows from left table and the matching rows from the right table and those row which does not match at that place it returns null value.
Ex:-here it returns all the row of class table and the matching row of right table.in last row the column does not match with right table column so it returns null value.
it returns all the rows of left table even single row does not math with right table.
take another table look like this
table name:-main
Ex:-we perform operation on class table and main table.no matches with right table it returns all row of left table and null value for right table.
RIGHT JOIN:-opposite of left join.it returns all the rows of right table and matching column of left table and returns null if the values did not matches.
Ex:-here we perform operation on class and sec table.
it returns all the row of right table and matching row of left table it compares on the basis of id column
if left table column not matches then it return null value.
it return all the row of right table even if there is no single match between both table.
Ex:-we perform operation on class table and main table.
it returns all the rows of right table and there is no single matches in left table so it return null in left.
FULL OUTER JOIN:-it returns all the rows of both table even if there is no match between the table.
if there is no match then it returns null as a value.
Ex:-we perform operation on class and sec table.
it return all row from both table if id is not match then it return null.
if there is no single match between both table then it returns all the rows of both table.
Ex:-we perform operation on class table and main table.it returns all the rows of both table.
No comments :
Post a Comment