Which three statements are true about a self join? (Choose three.)
Which three statements are true about a self join? (Choose three.)
A self join can use the ON clause, but it is not mandatory; it can also use a WHERE clause. The query must use two different aliases for the table to distinguish between the instances of the same table. A self join can be either an inner join or an outer join, as there is no restriction that a self join must be only one type of join. Therefore, the three correct statements are that the query must use two different aliases for the table, the ON clause can be used, and it can be an outer join.
B, E and F
Good Hint
B, E, F
B, D , E looks correct. I don't think self join can be outer.
The correct answers should be B, E and F a self join can also be an outer join. There is no difference for self joins. A self join is just two tables being joined. They just happen to be two copies of the same data
Self outer joins are also possible, and plausible. Figure a table with employees like: id name boss -- ----------- ------------- 1 John NULL 2 Paul 1 3 Scott 1 4 Kate 3 You can build a self outer join to retrieve a list of employees names and their bosses names, if any.
I meant to say BCE
a) false: you can use WHERE clause instead b) true: otherwise it reports ORA-00918 c) false: can be any condition d) false: can be any join e) true: can be used, but not necessary f) true: can be any join
I would like to understand why the ON clause "can" instead of "must" be used? My answer was A, B and F.
u can make self join using WHERE
Actually the Self Join CAN be the A, C and D. The word MUST makes the question wrong.
B,E,F 自己結合は内部も外部も対応してるのでDは誤りです。
A - FALSE; use OLD syntax, e.g. SELECT ... FROM employees e, employees m WHERE e.mgr=m.empno ... B - TRUE; when omitting, the error "ORA-00918: column ambiguously defined" appears C - FALSE; e.g. SELECT ... FROM employees e1, employees e2 WHERE e1.msal > e2.msal D - FALSE; valid query is e.g. SELECT e1.empno,e1.ename,e2.ename ... FROM employees e1 RIGHT OUTER JOIN employees e2 ON e1.mgr = e2.empno; E - TRUE; valid query is e.g. SELECT e.ename employee,e.bdate birth, m.ename manager FROM employees e JOIN employees m ON e.mgr = m.empno; F - TRUE; see answer B
The correct answers are B,E,F C and D are incorrect because it must not be an equijoin or an inner join. That is, it can be an equijoin , non-equijoin, inner join, outer join. A is incorrect, because it is not mandatory for an inner join to use the on clause, if you use old inner join syntax. That is, you can join two tables in a select, without using the word inner join and on clause.
B,C,F for me. Joining a table to itself on a condition is called a self join. A table is joined to itself to compare rows in the same table. The join condition would have to be an equijoin. If it is not there is another name for the join and it's called non equijoins. I guess a self non equijoin would be possible but this goes against the point I'm making. A self join is joining the same table to itself ON a condition and that is why it has to be an equijoin. For example the left outer joined table would show all the rows from the left and unmatched rows on the right filled with nulls where there was no matches. I don't think in self joins there would be any unmatched rows due to a missing column on the right. An outer self join is not possible because it's the same table your joining. Again, you can force a outer self join but this is more of an outer join than a self join.
When you are doing a self-join, keep in mind that you are joining two "individual" data sets. There exists no restriction within Oracle that forces you to use an ON clause join rather than a WHERE clause join, or an INNER join versus an OUTER join.
B,E,F is correct