Google Git
Sign in
apache / spark-website / 2c562896505588421cce257d111c8ba3805528c2 / . / site / docs / 3.4.3 / sql-ref-syntax-qry-select-join.html
blob: dfc00cd78ac475110fe87478dd9313b71e80c053 [file] [log] [blame]
<!DOCTYPE html>
<!--[if lt IE 7]> <html class="no-js lt-ie9 lt-ie8 lt-ie7"> <![endif]-->
<!--[if IE 7]> <html class="no-js lt-ie9 lt-ie8"> <![endif]-->
<!--[if IE 8]> <html class="no-js lt-ie9"> <![endif]-->
<!--[if gt IE 8]><!--> <html class="no-js"> <!--<![endif]-->
<head>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
<title>JOIN - Spark 3.4.3 Documentation</title>
<link rel="stylesheet" href="css/bootstrap.min.css">
<style>
body {
padding-top: 60px;
padding-bottom: 40px;
}
</style>
<meta name="viewport" content="width=device-width">
<link rel="stylesheet" href="css/main.css">
<script src="js/vendor/modernizr-2.6.1-respond-1.1.0.min.js"></script>
<link rel="stylesheet" href="css/pygments-default.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.css" />
<link rel="stylesheet" href="css/docsearch.css">
<!-- Matomo -->
<script>
var _paq = window._paq = window._paq || [];
/* tracker methods like "setCustomDimension" should be called before "trackPageView" */
_paq.push(["disableCookies"]);
_paq.push(['trackPageView']);
_paq.push(['enableLinkTracking']);
(function() {
var u="https://analytics.apache.org/";
_paq.push(['setTrackerUrl', u+'matomo.php']);
_paq.push(['setSiteId', '40']);
var d=document, g=d.createElement('script'), s=d.getElementsByTagName('script')[0];
g.async=true; g.src=u+'matomo.js'; s.parentNode.insertBefore(g,s);
})();
</script>
<!-- End Matomo Code -->
</head>
<body>
<!--[if lt IE 7]>
<p class="chromeframe">You are using an outdated browser. <a href="https://browsehappy.com/">Upgrade your browser today</a> or <a href="http://www.google.com/chromeframe/?redirect=true">install Google Chrome Frame</a> to better experience this site.</p>
<![endif]-->
<!-- This code is taken from http://twitter.github.com/bootstrap/examples/hero.html -->
<nav class="navbar fixed-top navbar-expand-md navbar-light bg-light" id="topbar">
<div class="container">
<div class="navbar-header">
<div class="navbar-brand"><a href="index.html">
<img src="img/spark-logo-hd.png" style="height:50px;"/></a><span class="version">3.4.3</span>
</div>
</div>
<button class="navbar-toggler" type="button" data-toggle="collapse"
data-target="#navbarCollapse" aria-controls="navbarCollapse"
aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarCollapse">
<ul class="navbar-nav">
<!--TODO(andyk): Add class="active" attribute to li some how.-->
<li class="nav-item"><a href="index.html" class="nav-link">Overview</a></li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarQuickStart" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Programming Guides</a>
<div class="dropdown-menu" aria-labelledby="navbarQuickStart">
<a class="dropdown-item" href="quick-start.html">Quick Start</a>
<a class="dropdown-item" href="rdd-programming-guide.html">RDDs, Accumulators, Broadcasts Vars</a>
<a class="dropdown-item" href="sql-programming-guide.html">SQL, DataFrames, and Datasets</a>
<a class="dropdown-item" href="structured-streaming-programming-guide.html">Structured Streaming</a>
<a class="dropdown-item" href="streaming-programming-guide.html">Spark Streaming (DStreams)</a>
<a class="dropdown-item" href="ml-guide.html">MLlib (Machine Learning)</a>
<a class="dropdown-item" href="graphx-programming-guide.html">GraphX (Graph Processing)</a>
<a class="dropdown-item" href="sparkr.html">SparkR (R on Spark)</a>
<a class="dropdown-item" href="api/python/getting_started/index.html">PySpark (Python on Spark)</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarAPIDocs" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">API Docs</a>
<div class="dropdown-menu" aria-labelledby="navbarAPIDocs">
<a class="dropdown-item" href="api/scala/org/apache/spark/index.html">Scala</a>
<a class="dropdown-item" href="api/java/index.html">Java</a>
<a class="dropdown-item" href="api/python/index.html">Python</a>
<a class="dropdown-item" href="api/R/index.html">R</a>
<a class="dropdown-item" href="api/sql/index.html">SQL, Built-in Functions</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarDeploying" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Deploying</a>
<div class="dropdown-menu" aria-labelledby="navbarDeploying">
<a class="dropdown-item" href="cluster-overview.html">Overview</a>
<a class="dropdown-item" href="submitting-applications.html">Submitting Applications</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="spark-standalone.html">Spark Standalone</a>
<a class="dropdown-item" href="running-on-mesos.html">Mesos</a>
<a class="dropdown-item" href="running-on-yarn.html">YARN</a>
<a class="dropdown-item" href="running-on-kubernetes.html">Kubernetes</a>
</div>
</li>
<li class="nav-item dropdown">
<a href="#" class="nav-link dropdown-toggle" id="navbarMore" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">More</a>
<div class="dropdown-menu" aria-labelledby="navbarMore">
<a class="dropdown-item" href="configuration.html">Configuration</a>
<a class="dropdown-item" href="monitoring.html">Monitoring</a>
<a class="dropdown-item" href="tuning.html">Tuning Guide</a>
<a class="dropdown-item" href="job-scheduling.html">Job Scheduling</a>
<a class="dropdown-item" href="security.html">Security</a>
<a class="dropdown-item" href="hardware-provisioning.html">Hardware Provisioning</a>
<a class="dropdown-item" href="migration-guide.html">Migration Guide</a>
<div class="dropdown-divider"></div>
<a class="dropdown-item" href="building-spark.html">Building Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/contributing.html">Contributing to Spark</a>
<a class="dropdown-item" href="https://spark.apache.org/third-party-projects.html">Third Party Projects</a>
</div>
</li>
<li class="nav-item">
<input type="text" id="docsearch-input" placeholder="Search the docs…">
</li>
</ul>
<!--<span class="navbar-text navbar-right"><span class="version-text">v3.4.3</span></span>-->
</div>
</div>
</nav>
<div class="container-wrapper">
<div class="left-menu-wrapper">
<div class="left-menu">
<h3><a href="sql-programming-guide.html">Spark SQL Guide</a></h3>
<ul>
<li>
<a href="sql-getting-started.html">
Getting Started
</a>
</li>
<li>
<a href="sql-data-sources.html">
Data Sources
</a>
</li>
<li>
<a href="sql-performance-tuning.html">
Performance Tuning
</a>
</li>
<li>
<a href="sql-distributed-sql-engine.html">
Distributed SQL Engine
</a>
</li>
<li>
<a href="sql-pyspark-pandas-with-arrow.html">
PySpark Usage Guide for Pandas with Apache Arrow
</a>
</li>
<li>
<a href="sql-migration-old.html">
Migration Guide
</a>
</li>
<li>
<a href="sql-ref.html">
SQL Reference
</a>
</li>
<ul>
<li>
<a href="sql-ref-ansi-compliance.html">
ANSI Compliance
</a>
</li>
<li>
<a href="sql-ref-datatypes.html">
Data Types
</a>
</li>
<li>
<a href="sql-ref-datetime-pattern.html">
Datetime Pattern
</a>
</li>
<li>
<a href="sql-ref-number-pattern.html">
Number Pattern
</a>
</li>
<li>
<a href="sql-ref-functions.html">
Functions
</a>
</li>
<li>
<a href="sql-ref-identifier.html">
Identifiers
</a>
</li>
<li>
<a href="sql-ref-literals.html">
Literals
</a>
</li>
<li>
<a href="sql-ref-null-semantics.html">
Null Semantics
</a>
</li>
<li>
<a href="sql-ref-syntax.html">
SQL Syntax
</a>
</li>
<ul>
<li>
<a href="sql-ref-syntax.html#ddl-statements">
Data Definition Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#dml-statements">
Data Manipulation Statements
</a>
</li>
<li>
<a href="sql-ref-syntax.html#data-retrieval-statements">
Data Retrieval(Queries)
</a>
</li>
<li>
<a href="sql-ref-syntax.html#auxiliary-statements">
Auxiliary Statements
</a>
</li>
</ul>
</ul>
<li>
<a href="sql-error-conditions.html">
Error Conditions
</a>
</li>
</ul>
</div>
</div>
<input id="nav-trigger" class="nav-trigger" checked type="checkbox">
<label for="nav-trigger"></label>
<div class="content-with-sidebar mr-3" id="content">
<h1 class="title">JOIN</h1>
<h3 id="description">Description</h3>
<p>A SQL join is used to combine rows from two relations based on join criteria. The following section describes the overall join syntax and the sub-sections cover different types of joins along with examples.</p>
<h3 id="syntax">Syntax</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="n">relation</span> <span class="err">{</span> <span class="p">[</span> <span class="n">join_type</span> <span class="p">]</span> <span class="k">JOIN</span> <span class="p">[</span> <span class="k">LATERAL</span> <span class="p">]</span> <span class="n">relation</span> <span class="p">[</span> <span class="n">join_criteria</span> <span class="p">]</span> <span class="o">|</span> <span class="k">NATURAL</span> <span class="n">join_type</span> <span class="k">JOIN</span> <span class="p">[</span> <span class="k">LATERAL</span> <span class="p">]</span> <span class="n">relation</span> <span class="err">}</span>
</code></pre></div></div>
<h3 id="parameters">Parameters</h3>
<ul>
<li>
<p><strong>relation</strong></p>
<p>Specifies the relation to be joined.</p>
</li>
<li>
<p><strong>join_type</strong></p>
<p>Specifies the join type.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">[ INNER ] | CROSS | LEFT [ OUTER ] | [ LEFT ] SEMI | RIGHT [ OUTER ] | FULL [ OUTER ] | [ LEFT ] ANTI</code></p>
</li>
<li>
<p><strong>join_criteria</strong></p>
<p>Specifies how the rows from one relation will be combined with the rows of another relation.</p>
<p><strong>Syntax:</strong> <code class="language-plaintext highlighter-rouge">ON boolean_expression | USING ( column_name [ , ... ] )</code></p>
<p><code class="language-plaintext highlighter-rouge">boolean_expression</code></p>
<p>Specifies an expression with a return type of boolean.</p>
</li>
</ul>
<h3 id="join-types">Join Types</h3>
<h4 id="inner-join"><strong>Inner Join</strong></h4>
<p>The inner join is the default join in Spark SQL. It selects rows that have matching values in both relations.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation [ INNER ] JOIN relation [ join_criteria ]</code></p>
<h4 id="left-join"><strong>Left Join</strong></h4>
<p>A left join returns all values from the left relation and the matched values from the right relation, or appends NULL if there is no match. It is also referred to as a left outer join.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation LEFT [ OUTER ] JOIN relation [ join_criteria ]</code></p>
<h4 id="right-join"><strong>Right Join</strong></h4>
<p>A right join returns all values from the right relation and the matched values from the left relation, or appends NULL if there is no match. It is also referred to as a right outer join.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation RIGHT [ OUTER ] JOIN relation [ join_criteria ]</code></p>
<h4 id="full-join"><strong>Full Join</strong></h4>
<p>A full join returns all values from both relations, appending NULL values on the side that does not have a match. It is also referred to as a full outer join.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation FULL [ OUTER ] JOIN relation [ join_criteria ]</code></p>
<h4 id="cross-join"><strong>Cross Join</strong></h4>
<p>A cross join returns the Cartesian product of two relations.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation CROSS JOIN relation [ join_criteria ]</code></p>
<h4 id="semi-join"><strong>Semi Join</strong></h4>
<p>A semi join returns values from the left side of the relation that has a match with the right. It is also referred to as a left semi join.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation [ LEFT ] SEMI JOIN relation [ join_criteria ]</code></p>
<h4 id="anti-join"><strong>Anti Join</strong></h4>
<p>An anti join returns values from the left relation that has no match with the right. It is also referred to as a left anti join.</p>
<p><strong>Syntax:</strong></p>
<p><code class="language-plaintext highlighter-rouge">relation [ LEFT ] ANTI JOIN relation [ join_criteria ]</code></p>
<h3 id="examples">Examples</h3>
<div class="language-sql highlighter-rouge"><div class="highlight"><pre class="highlight"><code><span class="c1">-- Use employee and department tables to demonstrate different type of joins.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">employee</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">6</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">department</span><span class="p">;</span>
<span class="o">+</span><span class="c1">------+-----------+</span>
<span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+-----------+</span>
<span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">+</span><span class="c1">------+-----------+</span>
<span class="c1">-- Use employee and department tables to demonstrate inner join.</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span><span class="p">,</span> <span class="n">deptname</span>
<span class="k">FROM</span> <span class="n">employee</span> <span class="k">INNER</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="c1">-- Use employee and department tables to demonstrate left join.</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span><span class="p">,</span> <span class="n">deptname</span>
<span class="k">FROM</span> <span class="n">employee</span> <span class="k">LEFT</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">6</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="c1">-- Use employee and department tables to demonstrate right join.</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span><span class="p">,</span> <span class="n">deptname</span>
<span class="k">FROM</span> <span class="n">employee</span> <span class="k">RIGHT</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="c1">-- Use employee and department tables to demonstrate full join.</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span><span class="p">,</span> <span class="n">deptname</span>
<span class="k">FROM</span> <span class="n">employee</span> <span class="k">FULL</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">6</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="k">NULL</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="c1">-- Use employee and department tables to demonstrate cross join.</span>
<span class="k">SELECT</span> <span class="n">id</span><span class="p">,</span> <span class="n">name</span><span class="p">,</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span><span class="p">,</span> <span class="n">deptname</span> <span class="k">FROM</span> <span class="n">employee</span> <span class="k">CROSS</span> <span class="k">JOIN</span> <span class="n">department</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span> <span class="n">deptname</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span><span class="n">Engineering</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="n">Marketing</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span> <span class="n">Sales</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+-----------|</span>
<span class="c1">-- Use employee and department tables to demonstrate semi join.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">employee</span> <span class="n">SEMI</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span><span class="mi">103</span><span class="o">|</span> <span class="n">Paul</span><span class="o">|</span> <span class="mi">3</span><span class="o">|</span>
<span class="o">|</span><span class="mi">101</span><span class="o">|</span> <span class="n">John</span><span class="o">|</span> <span class="mi">1</span><span class="o">|</span>
<span class="o">|</span><span class="mi">102</span><span class="o">|</span> <span class="n">Lisa</span><span class="o">|</span> <span class="mi">2</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="c1">-- Use employee and department tables to demonstrate anti join.</span>
<span class="k">SELECT</span> <span class="o">*</span> <span class="k">FROM</span> <span class="n">employee</span> <span class="n">ANTI</span> <span class="k">JOIN</span> <span class="n">department</span> <span class="k">ON</span> <span class="n">employee</span><span class="p">.</span><span class="n">deptno</span> <span class="o">=</span> <span class="n">department</span><span class="p">.</span><span class="n">deptno</span><span class="p">;</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span> <span class="n">id</span><span class="o">|</span> <span class="n">name</span><span class="o">|</span><span class="n">deptno</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
<span class="o">|</span><span class="mi">105</span><span class="o">|</span><span class="n">Chloe</span><span class="o">|</span> <span class="mi">5</span><span class="o">|</span>
<span class="o">|</span><span class="mi">104</span><span class="o">|</span> <span class="n">Evan</span><span class="o">|</span> <span class="mi">4</span><span class="o">|</span>
<span class="o">|</span><span class="mi">106</span><span class="o">|</span> <span class="n">Amy</span><span class="o">|</span> <span class="mi">6</span><span class="o">|</span>
<span class="o">+</span><span class="c1">---+-----+------+</span>
</code></pre></div></div>
<h3 id="related-statements">Related Statements</h3>
<ul>
<li><a href="sql-ref-syntax-qry-select.html">SELECT</a></li>
<li><a href="sql-ref-syntax-qry-select-hints.html">Hints</a></li>
<li><a href="sql-ref-syntax-qry-select-lateral-subquery.html">LATERAL Subquery</a></li>
</ul>
</div>
<!-- /container -->
</div>
<script src="js/vendor/jquery-3.5.1.min.js"></script>
<script src="js/vendor/bootstrap.bundle.min.js"></script>
<script src="js/vendor/anchor.min.js"></script>
<script src="js/main.js"></script>
<script type="text/javascript" src="https://cdn.jsdelivr.net/npm/docsearch.js@2/dist/cdn/docsearch.min.js"></script>
<script type="text/javascript">
// DocSearch is entirely free and automated. DocSearch is built in two parts:
// 1. a crawler which we run on our own infrastructure every 24 hours. It follows every link
// in your website and extract content from every page it traverses. It then pushes this
// content to an Algolia index.
// 2. a JavaScript snippet to be inserted in your website that will bind this Algolia index
// to your search input and display its results in a dropdown UI. If you want to find more
// details on how works DocSearch, check the docs of DocSearch.
docsearch({
apiKey: 'd62f962a82bc9abb53471cb7b89da35e',
appId: 'RAI69RXRSK',
indexName: 'apache_spark',
inputSelector: '#docsearch-input',
enhancedSearchInput: true,
algoliaOptions: {
'facetFilters': ["version:3.4.3"]
},
debug: false // Set debug to true if you want to inspect the dropdown
});
</script>
<!-- MathJax Section -->
<script type="text/x-mathjax-config">
MathJax.Hub.Config({
TeX: { equationNumbers: { autoNumber: "AMS" } }
});
</script>
<script>
// Note that we load MathJax this way to work with local file (file://), HTTP and HTTPS.
// We could use "//cdn.mathjax...", but that won't support "file://".
(function(d, script) {
script = d.createElement('script');
script.type = 'text/javascript';
script.async = true;
script.onload = function(){
MathJax.Hub.Config({
tex2jax: {
inlineMath: [ ["$", "$"], ["\\\\(","\\\\)"] ],
displayMath: [ ["$$","$$"], ["\\[", "\\]"] ],
processEscapes: true,
skipTags: ['script', 'noscript', 'style', 'textarea', 'pre']
}
});
};
script.src = ('https:' == document.location.protocol ? 'https://' : 'http://') +
'cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js' +
'?config=TeX-AMS-MML_HTMLorMML';
d.getElementsByTagName('head')[0].appendChild(script);
}(document));
</script>
</body>
</html>