If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Did said developer express any rational reason for the requirement to avoid UNION? I ask just in case there's anything else that he'd prefer us not to use: any particular letter of the alphabet, or whatever?

How about if you outer-joined the user table to the two other tables, and checked for rows that match in neither one? You might find it more efficient to outer-join to an in-line view, and you might also require a distinct on the final result set -- hopefully that won't contravene your developers religious convictions

Did said developer express any rational reason for the requirement to avoid UNION? I ask just in case there's anything else that he'd prefer us not to use: any particular letter of the alphabet, or whatever?

I prefer writing all of my select statements without using the letter "E"! I'm sure I get better performance that way as "E" is a very inEfficient letter!

object :
i have written the query using union clause.
but developer want to written in sql without using union .ie. using joins

You need to explain to him that a union is not the same as an inner join. In an inner join each row returned can be from any of the tables being joined. When you do a union, each row returned is from one table or the other, but not both. However given the way you wrote the query, you can also express it with two not in clauses, or better yet not exists clauses one for each table.

I think you can do it if you create a new database, setup a dblink, and then insert into a new table one of the original tables plus the result from a minus (make sure you go the right way on that) of the two original tables. And then to be on the safe side of things, make sure the new database is on a different platform and using a different version. This should completely avoid that nasty union relational operator thingy.