際際滷

際際滷Share a Scribd company logo

Relational algebra

Download as PPTX, PDF
Joshi Vinay
Joshi Vinay

Joins allow tables to be combined if they share a common field with a relationship. The main types of joins are cross, equi, natural, and outer joins. Cross joins return the Cartesian product of all rows between tables, equi joins match rows on equality of fields, natural joins match on common fields, and outer joins return all rows including those without matches.

1 of 31
Download to read offline
RELATIONAL ALGEBRA
Relational algebra
Relational algebra
Relational algebra
Relational algebra
Relational algebra
Relational algebra
Relational algebra
Relational algebra
JOINS A JOIN works on two or more tables if they have at least one common field and have a relationship between them. JOIN keeps the base tables (structure and data) unchanged.
Types of Joins 1.cross Join 2. equi join 3. natural join 4. Outer join(left outer join,right outer join,full outer join)
Cross join Cross join is also know an cross product or Cartesian product In sql cross join is default implemented with out using the clause CROSS JOIN. how cross join is applicable on Relation or a table?
Cross Join(X) sid sname 1 joshi 2 vinay cid cname sid 1 c 1 2 java 2 3 dbms 1 sid sname cid cname sid 1 joshi 1 c 1 1 joshi 2 java 2 1 joshi 3 dbms 1 2 vinay 1 c 1 2 vinay 2 java 2 2 vinay 3 dbms 1
Working of cross Join Number of columns in resultant table=m+n Example : In above table the resultant table would contain 5 coloumns i.e two columns from table1+three column from from table2 Result =2+3=5
Number of rows in resultant table = M*N i.e [individual row in table1] * [All rows in table2] Example: In above example total number of rows is M* N=6 i.E 2 rows from table1 * 3 rows in table2=6 i.E result= 2*3=6
Syntax of cross join With out using cross join syntax looks like: Select * from student,course; Syntax: select * from student cross join course;
Equi Join(=) SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality. Syntax: select column1,column2 from table1,table2 where table1.column1=table2.column1; Example :select sname,cname from table1, table2 where table1.sid=table2.sid; Or Select sname,cname from table1 join table2 on(table1.sid=table2.sid);
Natural join We have already learned that an EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as comparison operator in the where clause to refer equality. The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only. Syntax: select * from table1 natural join table2; Example 1: select * from table1 natural join table2; Example2: select sname,cname from table1 natural join table2;
Outer JOINS 1.left outer joins 2. right outer joins 3.full outer joins
1.Left outer joins SQL LEFT join fetches a complete set of records from table1, with the matching records (depending on the availability) in table2. The result is NULL in the right side when no matching will take place.
Left outer join sid sname 1 joshi 2 vinay 3 ganesh cid cname sid 1 c 1 2 java 2 3 dbms 1 4 python sid sname cid cname sid 1 joshi 1 c 1 2 vinay 2 java 2 1 joshi 3 dbms 1 3 ganesh
Syntax for left outer join Select * from tab1 left outer join tab2 ON tab1.column1=tab2.column1; Example: select * from student left outer join course on student.sid=course.sid; Example2: select sname,cname from tab1 left outer join tab2 on tab1.sid=tab2.sid where sname='raghu';
2. Right outer join The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables ( before and after the JOIN clause mentioned in the syntax below) , and the unmatched rows will also be available from the table written after the JOIN clause ( mentioned in the syntax below ).
Right outer JOIN sid sname 1 joshi 2 vinay 3 ganesh cid cname sid 1 c 1 2 java 2 3 dbms 1 4 python sid sname cid cname sid 1 joshi 3 dbms 1 1 joshi 1 c 1 2 vinay 2 java 2 4 python
Syntax for right outer join Select * from table1 right outer join table2 on table1.column1=table2.column1; Example 1: select * from student right outer join course on student.sid=course.sid; Example2: select sname,cname from student right outer join course on student.sid=course.sid where student.sname=ganesh;
Full outer join The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables ( before and after the JOIN clause mentioned in the syntax below) , and the unmatched rows will also be available from the table written after the JOIN clause ( mentioned in the syntax below ).
Full outer JOIN sid sname cid cname sid 1 joshi 1 c 1 2 vinay 2 java 2 1 joshi 3 dbms 1 3 ganesh 4 python
Syntax for full outer join Select * from table1 full outer join table2 on table1.column1=tab2.column; Example1: select * from student full outer join course on student.sid=course.sid; Example2: select * from student full outer join course on student.sid=course.sid where sname=ganesh;
Over all VIEW on outer joins
Inner Join Inner join is a join which displays the common part of rows matching as a resultant. Inner joins can be implemented as Example1: Select * from table1 inner join table2 on table1.sid=table2.sid; Example2: select * from table1,table2 where table1.sid=table2.sid; Example 3: select * from table1 cross join table2 where table1.sid=table2.sid;
Relational algebra

More Related Content

Relational algebra

  • 10. JOINS A JOIN works on two or more tables if they have at least one common field and have a relationship between them. JOIN keeps the base tables (structure and data) unchanged.
  • 11. Types of Joins 1.cross Join 2. equi join 3. natural join 4. Outer join(left outer join,right outer join,full outer join)
  • 12. Cross join Cross join is also know an cross product or Cartesian product In sql cross join is default implemented with out using the clause CROSS JOIN. how cross join is applicable on Relation or a table?
  • 13. Cross Join(X) sid sname 1 joshi 2 vinay cid cname sid 1 c 1 2 java 2 3 dbms 1 sid sname cid cname sid 1 joshi 1 c 1 1 joshi 2 java 2 1 joshi 3 dbms 1 2 vinay 1 c 1 2 vinay 2 java 2 2 vinay 3 dbms 1
  • 14. Working of cross Join Number of columns in resultant table=m+n Example : In above table the resultant table would contain 5 coloumns i.e two columns from table1+three column from from table2 Result =2+3=5
  • 15. Number of rows in resultant table = M*N i.e [individual row in table1] * [All rows in table2] Example: In above example total number of rows is M* N=6 i.E 2 rows from table1 * 3 rows in table2=6 i.E result= 2*3=6
  • 16. Syntax of cross join With out using cross join syntax looks like: Select * from student,course; Syntax: select * from student cross join course;
  • 17. Equi Join(=) SQL EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables. An equal sign (=) is used as comparison operator in the where clause to refer equality. Syntax: select column1,column2 from table1,table2 where table1.column1=table2.column1; Example :select sname,cname from table1, table2 where table1.sid=table2.sid; Or Select sname,cname from table1 join table2 on(table1.sid=table2.sid);
  • 18. Natural join We have already learned that an EQUI JOIN performs a JOIN against equality or matching column(s) values of the associated tables and an equal sign (=) is used as comparison operator in the where clause to refer equality. The SQL NATURAL JOIN is a type of EQUI JOIN and is structured in such a way that, columns with the same name of associated tables will appear once only. Syntax: select * from table1 natural join table2; Example 1: select * from table1 natural join table2; Example2: select sname,cname from table1 natural join table2;
  • 19. Outer JOINS 1.left outer joins 2. right outer joins 3.full outer joins
  • 20. 1.Left outer joins SQL LEFT join fetches a complete set of records from table1, with the matching records (depending on the availability) in table2. The result is NULL in the right side when no matching will take place.
  • 21. Left outer join sid sname 1 joshi 2 vinay 3 ganesh cid cname sid 1 c 1 2 java 2 3 dbms 1 4 python sid sname cid cname sid 1 joshi 1 c 1 2 vinay 2 java 2 1 joshi 3 dbms 1 3 ganesh
  • 22. Syntax for left outer join Select * from tab1 left outer join tab2 ON tab1.column1=tab2.column1; Example: select * from student left outer join course on student.sid=course.sid; Example2: select sname,cname from tab1 left outer join tab2 on tab1.sid=tab2.sid where sname='raghu';
  • 23. 2. Right outer join The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables ( before and after the JOIN clause mentioned in the syntax below) , and the unmatched rows will also be available from the table written after the JOIN clause ( mentioned in the syntax below ).
  • 24. Right outer JOIN sid sname 1 joshi 2 vinay 3 ganesh cid cname sid 1 c 1 2 java 2 3 dbms 1 4 python sid sname cid cname sid 1 joshi 3 dbms 1 1 joshi 1 c 1 2 vinay 2 java 2 4 python
  • 25. Syntax for right outer join Select * from table1 right outer join table2 on table1.column1=table2.column1; Example 1: select * from student right outer join course on student.sid=course.sid; Example2: select sname,cname from student right outer join course on student.sid=course.sid where student.sname=ganesh;
  • 26. Full outer join The SQL RIGHT JOIN, joins two tables and fetches rows based on a condition, which is matching in both the tables ( before and after the JOIN clause mentioned in the syntax below) , and the unmatched rows will also be available from the table written after the JOIN clause ( mentioned in the syntax below ).
  • 27. Full outer JOIN sid sname cid cname sid 1 joshi 1 c 1 2 vinay 2 java 2 1 joshi 3 dbms 1 3 ganesh 4 python
  • 28. Syntax for full outer join Select * from table1 full outer join table2 on table1.column1=tab2.column; Example1: select * from student full outer join course on student.sid=course.sid; Example2: select * from student full outer join course on student.sid=course.sid where sname=ganesh;
  • 29. Over all VIEW on outer joins
  • 30. Inner Join Inner join is a join which displays the common part of rows matching as a resultant. Inner joins can be implemented as Example1: Select * from table1 inner join table2 on table1.sid=table2.sid; Example2: select * from table1,table2 where table1.sid=table2.sid; Example 3: select * from table1 cross join table2 where table1.sid=table2.sid;