Answer Posted / rekha d rathnam
1.Cross Join - is a join without a join contition
SELECT * FROM table-1 CROSS JOIN table-2
2.Inner Join - return only matched records
i.Equi-Join
ii.Non-Equi Join
* Inner joins or equi joins are the most common type of
joins, they use equality "=" of common attributes to join
tables.
SELECT projects.name AS "Project Name", teachers.name
FROM teachers, projects
WHERE teachers.id = projects.teacher;
* Like an inner join, but with an operator different
from "=" in the condition, e.g., not equal "<>".
SELECT projects.name AS "Project Name", teachers.name
FROM teachers, projects
WHERE teachers.id != projects.teacher;
3.Outer Join
i.Full Outer Join
11.Left Outer Join
iii.Right Outer Join
LEFT -- only unmatched rows from the left side table (table-
1) are retained
RIGHT -- only unmatched rows from the right side table
(table-2) are retained
FULL -- unmatched rows from both tables (table-1 and table-
2) are retained
SELECT assign.project, projects.name, assign.percentage
FROM projects LEFT OUTER JOIN assign
ON projects.id = assign.project ;
4.Self Join - With table aliases you can join a table to
itself.
SELECT DISTINCT *
FROM assign a, assign b
WHERE a.stud = 2 AND b.stud = 4 AND a.project = b.project;
5.Multi Join - The number of tables involved in a join are
not restricted.
SELECT DISTINCT assign.project, projects.name AS "Project",
assign.percentage, stud.name AS "Student"
FROM projects, assign, stud
WHERE projects.id = 1 AND projects.id = assign.project
AND assign.stud = stud.id ;
Is This Answer Correct ? | 13 Yes | 0 No |
Post New Answer View All Answers
What is lookup table in sql?
Does google use sql?
What program will open a mdb file?
what are the authentication modes in sql server? How can it be changed? : Sql dba
explain advantages of myisam over innodb? : Sql dba
What does closing a cursor do?
what is 'mysqlcheck'? : Sql dba
What are the subsets of sql?
What is the difference between an inner and outer join?
How does stored procedure reduce network traffic?
What is record variable?
How can you fetch first 5 characters of the string?
How many sectors are in a partition?
How do you rename a table in sql?
how mysql optimizes distinct? : Sql dba