If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set. Resulting set for outer join The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the ...
Append [All |Distinct]saveAppend used with Subquery Scan to implement UNION and UNION ALL queries. Can operate from disk based on virtue of "save". Miscellaneous/Other: HashhashUsed for inner joins and left and right outer joins (provides input to a hash join). The Hash operator creates th...
Plan nodes that refer to execution on compute nodes are prefixed with XN in the EXPLAIN output. info character(400) Qualifier and filter information for the plan node. For example, join conditions and WHERE clause restrictions are included in this column. Sample queries Consider the ...
TheSELECTis associated with the row in non-extendedEXPLAINoutput that has anidvalue ofN. outer_tablessemi join (inner_tables) A semijoin operation.inner_tablesshows the tables that were not pulled out. SeeSection 8.2.2.1, “Optimizing Subqueries, Derived Tables, and View References with Semijoin...
Replace invalid characters with dashes, * so that for example "I/O Read Time" becomes "I-O-Read-Time". */static voidExplainXMLTag(const char *tagname, int flags, ExplainState *es){ const char *s; const char *valid = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789-_."; ...
above. As an analogy to the coding world, you can look at the concept of an inner join as very similar to a nested loop. MySQL chooses the table it thinks will be best to start the journey with (the outer "loop") and then touches the next table using the values from the outer "...
For example: CREATE TABLE t1(a INT, b INT); CREATE TABLE t2(a INT, b INT, INDEX ia(a)); EXPLAIN SELECT /*+ INL_JOIN(t2) */ * FROM t1 JOIN t2 ON t1.a = t2.a; EXPLAIN SELECT (SELECT a FROM t2 WHERE t2.a = t1.b LIMIT 1) FROM t1; -- Before v6.4.0: +---+--...
This type of join is usually used for queries with the EXISTS clause. When you view execution plans of hash join operations, you also need to focus on the child nodes. hash cond: the join condition. Example: hash cond(tmp.a=tmp1.b). hash key: the key that is used for hash ...
With the help ofEXPLAIN, you can see where you should add indexes to tables so that thestatement executes faster by using indexes to find rows. You can also useEXPLAIN to check whether the optimizer joins the tables in an optimal order. Togive a hint to the optimizer to use a join orde...
SORT JOIN TABLE ACCESS FULL emp SORT JOIN TABLE ACCESS FULL dept Notice that the object_node and other columns are empty Sample Query #2 (Query #1 with parallel hints) select/*+ parallel(B,4) parallel(A,4) */ A.dname, avg(B.sal), max(B.sal) ...