Download Sql Tutorials,Download Oracle Tutorials,Download Sql Ebooks,Download Oracle Ebooks

SQL RIGHT JOIN

oracle.certification2007@gmail.com (Oramaster) — Fri, 27 Feb 2009 03:53:00 +0000

As you have learned about the SQL LEFT JOIN where all the rows are returned from the left table ever when there is no match from the right table, the same case is with the SQL RIGHT JOIN, but here all the rows from the right side table is returned without matching the left side table. Let start with the syntax:

Syntax:-
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

Now look at the two tables, TABLE A and TABLE B:

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	City 1
2	GHI	JKL	Street 20	City 2
3	MNO	PQR	Street 30	City 3

TABLE B

O_Id	OrderNo	P_Id
1	778951	3
2	446781	3
3	224561	1
4	245621	1
5	347641	15

Now we apply the SQL RIGHT JOIN query:

SELECT TABLE A.LastName, TABLE A.FirstName, TABLE B.OrderNo
FROM TABLE A
RIGHT JOIN TABLE B
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName;

The above query will return all the rows from TABLE A even there is no match with TABLE B. That does not mean that no check for common rows are made. The result of the above query will be:

OUTPUT

LastName	FirstName	OrderNo
ABC	DEF	224561
ABC	DEF	245621
MNO	PQR	778951
MNO	PQR	446781
		347641

NOTE: In some places SQL RIGHT JOIN is also refered as RIGHT OUTER JOIN.

SQL LEFT JOIN

oracle.certification2007@gmail.com (Oramaster) — Tue, 24 Feb 2009 09:51:00 +0000

This article expect that you have a prior knowledge of JOINING the table.

Before we start let start with the syntax of SQL LEFT JOIN, because this will help to understand the rest of the article quite easily

SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

The major part of the above syntax is " LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name;"
Notice the keyword LEFT JOIN , what does this mean ? LEFT JOIN returns all the rows from left table even though the rows in the right table do not exist. To explain it clearly lets take an example:

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	City 1
2	GHI	JKL	Street 20	City 2
3	MNO	PQR	Street 30	City 3

TABLE B

O_Id	OrderNo	P_Id
1	778951	3
2	446781	3
3	224561	1
4	245621	1
5	347641	15

Now if we join two tables on the above provided Syntax, i.e.

SELECT TABLE A.LastName, TABLE A.FirstName, TABLE B.OrderNo
FROM TABLE A
LEFT JOIN Orders
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName ;

Yes, you guessed it correctly that all the rows from the TABLE A will be returned without taking into consideration that those rows exits in the TABLE B

The OUTPUT will be

LastName	FirstName	OrderNo
ABC	DEF	224561
ABC	DEF	245621
MNO	PQR	778951
MNO	PQR	446781
		347641

NOTE: In some places SQL LEFT JOIN is also refered as LEFT OUTER JOIN.

INNER JOIN

oracle.certification2007@gmail.com (Oramaster) — Tue, 17 Feb 2009 06:22:00 +0000

Today let's discuss on the advance and the most important part that is how to join the two tables. Their are many ways to join a table but it is up to you what type of joining do you need The two type of joining is the INNER JOIN and OUTER JOIN.

Lets start with INNER JOIN.
By the term inner join we mean joining two or more table under some condition i.e through WHERE clause. It is just like a JOIN statement. Lets see the example

Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	PPP
2	GHI	IJK	Street 20	QQQ
3	LMN	OPQ	Street 30	DDD

TABLE B

O_Id	OrderNo	P_Id
1	111111	1
2	222222	2
3	444444	3
4	555555	4
5	666666	5

Now we ask for INNER JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM TABLE A
INNER JOIN TABLE B
ON TABLE A.P_Id=TABLE B.P_Id
ORDER BY TABLE A.LastName

The out put that it will return will be

LastName	FirstName	OrderNo
ABC	DEF	111111
GHI	IJK	222222
LMN	OPQ	333333

The INNER JOIN keyword will return the number of rows that are common in both the table , like here the column that are returned are of two common people Hansen, Pettersen.

SQL Join Tables

oracle.certification2007@gmail.com (Oramaster) — Mon, 16 Feb 2009 05:36:00 +0000

In SQL join keyword is used quite offen. It is used to join two or more table together. There may be situation when you would like to join two or more table to get the common data or to verify some query.

Lets take this example.

This is a basic example and the complexities depend on the level of query that you want.

TABLE A

P_Id	LastName	FirstName	Address	City
1	ABC	DEF	Street 10	PPP
2	GHI	IJK	Street 20	QQQ
3	LMN	OPQ	Street 30	DDD

TABLE B

O_Id	OrderNo	P_Id
1	111111	1
2	222222	2
3	444444	3
4	555555	4
5	666666	5

To join a table, the most important thing to remember is to join the table on primary key. I am saying this because we want to join table on unique ID and primary key sufies this situation. As in the TABLE A table P_ID is unique ID and no two employees can have the same, so we join the table on this bases. If this condition is not taken into account the table will still join and provide the out put but that would be a catisian product i.e all the tables in TABLE A and all the tables in TABLE B. Another thing to remember is that both the tables should have common columb, on which you could join the table. As you can see TABLE A and TABLE B have P_ID is common, so the common table will be returned.

Syntax
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name;

For this example
SELECT P_ID
FROM TABLE A
INNER JOIN TABLE B
ON TABLE A.P_ID=TABLE B.P_ID;

SQL Operators

oracle.certification2007@gmail.com (Oramaster) — Mon, 03 Nov 2008 08:50:00 +0000

These operators are generally used in SQL like with WHERE clause. You can use any one operator or any number of operators (Unless it is logically and Syntax Correct)

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	If you know the exact value you want to return for at least one of the columns

SQL Quiz

oracle.certification2007@gmail.com (Oramaster) — Fri, 28 Dec 2007 06:39:00 +0000

If you think aor plannig to give the SQL Certification Paper than this is the right place to practice the online Dumps. Get the latest paper question , latest trends and this quiz will garantee you that you pass the certification without any problem. If any way you are not able to qualify the paper don't worry this page has another links that will help you to prepare on you weak spots.

Check out these links :

SQL Advance Quiz
Check your knowledge of SQL
www.quia.com

Oracle SQL Quiz
SQL Fundamental Quiz check out !!!
www.w3schools.com

SQL Quiz
Let's try this and find out your score.
www.geekinterview.com

Free SQl Dumps

oracle.certification2007@gmail.com (Oramaster) — Fri, 28 Dec 2007 05:22:00 +0000

If you are looking for sql dumps than you have reached the right place. Here you can download the free oracle dumps, free sql dumps, download dumps, free 1Z0-007 dumps, oracle latest dumps, testking dumps, dump master, sql dumpster, etc. The link is already provided on the blog, but for your convinence please Click Here .

About Blog

oracle.certification2007@gmail.com (Oramaster) — Fri, 07 Dec 2007 13:36:00 +0000

This blog deals in following matter like: certification, oracle certification materials, free oracle certifications material, oracle oca certification, oracle certification course, certification oracle, free books of oracle certification, free self study oracle certification, oracle tutorials, oracle ebooks, oracle 9i tutorials for beginners, oracle 9i tutorials, oracle database tutorials, oracle dba university online tutorials, oracle developer tutorials, oracle tutorials pdf, sql free notes or tutorials, sql tutorials, sql tutorials, free sql tutorials, sql free notes, dowload sql tutorials, sql study guides, sql books

Post new comment

oracle.certification2007@gmail.com (Oramaster) — Mon, 26 Nov 2007 06:32:00 +0000

Please do comment on the Blogger or If you need any other oracle material to list with your name .