Outer,Right join

VIVEKANANDHAN_PERIASAMY
VIVEKANANDHAN_PERIASAMY used Ask the Experts™
on
How to write a query without using these right/left joins functions?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
dsackerContract ERP Admin/Consultant

Commented:
The outer joins are when you want all the rows of one table, but only the rows of the other table that match. If you just code a JOIN or INNER JOIN, that will only return rows for which both tables match (on what you join).

However, if you really don't like even the JOIN command, and you KNOW for sure that you always want the INNER JOIN (type of join), you can always code the join "old style" within the WHERE clause.

select table1.myfield1,
       table2.myfield2
from   table1,
       table2
where  table1.key = table2.key
and    table1.somefield = 'some criteria'

Open in new window

Ephraim WangoyaSoftware Engineer

Commented:

select a.*, b.*
from table1 a
join table2 b on b.id=a.id
Olaf DoschkeSoftware Developer

Commented:
actually joins are, what sql server does very good, so why avoid them? It's the nature of database normalisation for a  online transactional processing database to put data into related tables.

If you want all data pulled together already, use datawarehousing, eg in sql server: olap cubes and MDX instead of SQL. It's a totally different point of view on the same data, but does not best fit the purpose of non relational and non redundant storage of live data, but more for statistical analysis and data mining.

Bye, Olaf.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

i just like to know writing different types of queries.


Actually my question was how to write left or outer join without using a functions?

ewangoya:has already given an good example of that.but is it possible to join more than 2 tables?if so how?
Ephraim WangoyaSoftware Engineer

Commented:

you can join multiple tables, the joined fields depend on what data you want to return

select a.*, b.*, c.*
from table1 a
join table2 b on b.id = a.id
join table3 c on c.id = b.id

or

select a.*, b.*, c.*
from table1 a
join table2 b on b.id = a.id
join table3 c on c.id = a.id
again here we are using join function.I want to write a query without using join functions
Ephraim WangoyaSoftware Engineer

Commented:


select a.*, b.*, c.*
from table1 a,  table2 b, table3 c
where b.id = a.id
where c.id = b.id
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
If you are talking about outer joins: There is a proprietary syntax, obsolete with MSSQL 2008 (and does no longer work if you have a Database Compatibility Level of MSSQL 2008, which is the default), so it is not really a good idea to use them anymore without real need.
   select ... from tblA, tblB where tblA.a *= tblB.a
is the same as
   select ...  from tblA left join tblB on tblA.a = tblB.a
i tried this query but null value isn't displaying as a we do by joins functions

Commented:
An outer join is basically just a different syntax for a join and a union. Example:

SELECT t1.a,t1.b,t1.c,t2.d
FROM t1
LEFT JOIN t2
ON t1.a=t2.a;

means the same as:

SELECT t1.a,t1.b,t1.c,t2.d
FROM t1,t2
WHERE t1.a = t2.a
UNION ALL
SELECT t1.a,t1.b,t1.c, NULL
FROM t1
WHERE NOT EXISTS (SELECT * FROM t2 WHERE t1.a = t2.a);
Olaf DoschkeSoftware Developer

Commented:
You can't do a outer join without join. Joining in the Where clause always is an inner join, both sides must and do provide data into the joined result record.

Therefor I can only second Qlemo, the answer you accepted can't be the answer you want, besides including no JOIN, it does an inner join only, not an outer join.

Bye, Olaf.

Commented:
The accepted answer isn't even valid SQL (multiple WHERE clauses).
dsackerContract ERP Admin/Consultant

Commented:
Smacks of insider trading, IMO.
Right join:by using where clause
select a.*,b.* from supply1 as a,supply2 as b where a.supplyid=*b.supplyid

left join:
select a.*,b.* from supply1 as a,supply2 as b where a.supplyid*=b.supplyid

this is the answer for my question.
dsackerContract ERP Admin/Consultant

Commented:
The very first post after your question talked about using the WHERE clause. Was that NOT your answer?

Besides, the WHERE clause does an INNER join and an INNER join ONLY. No other kind. You are given misleading information if you've been lead to think that is a right join by using a where clause.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
dsacker,
if you read http:#a36322771 you will see it is possible to implement outer joins in the Where clause . The syntax is proprietary for MSSQL, and only works in db compatibility levels below MSSQL 2005.

VIVEKANANDHAN_PERIASAMY,
isn't my answer saying (almost) the same as you in http:#a36356723 ?
dsackerContract ERP Admin/Consultant

Commented:
Well, certainly you can join with a subselect, or you can create a temp table that unions all your rows from your parent select, or a number of ways.

However, the zones designated for this question are SQL 2005 and SQL 2008, and nothing was mentioned regarding this being in line with compatibility levels. It was somewhat normal to assume normalcy, wouldn't you think?

My point stands.
dsackerContract ERP Admin/Consultant

Commented:
Besides, his reasoning for selecting who he awarded is ambiguous enough, IMO, to not contest it any further.
Guys! i just want a joind to be wrritten in normal statement without using join fuction, by above query i am able to achieve it. but this statement cannot be expressed in 2005 and 2008.

And dsacker: I am not misleading anyone.Only after testing the result i have posted my thought
dsackerContract ERP Admin/Consultant

Commented:
Best wishes.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
"You cannot do that" is not correct if you can set it up to work. Switching compatibility level is an option, isn't it? My answer hence is the only precise one, though it is missing the detail that the proprietary syntax is obsolete starting with MSSQL 2005, not only 2008.

VIVEKANANDHAN_PERIASAMY, you accepted the wrong answer. Point. My answer is an option, the UNION is an option, so there are enough candidates for answers being accepted. "cannot be expressed in 2005 and 2008" is not correct, it should be extended to "as long as you do not ..."
Qlemo: what do you want me do do know?
Olaf DoschkeSoftware Developer

Commented:
VIVEKANANDHAN_PERIASAMY,

the only problem with your answer choice actually was, that the =* or *= syntax answer was given by Qlemo and you choose another answer. I think it was ewangoya's answer you chose, and this didn't contain that syntax.

So, simply award points to Qlemo for his answer ID:36322771 and everything's fine.

Bye, Olaf.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
You need to accept those answer(s) which fit your question best. From my POV, both http:#a36322771 and http:#a36341690 provide valid alternatives to using Ansi Join. The latter works always, independent from the db compat level.
Olaf DoschkeSoftware Developer

Commented:
Well, that's even fairer than I would have been.

Bye, Olaf.
Thanks Olaf! I am much satisfied in your approach. putting it in a kind way.
i will award the points to Qlemo

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial