Below is a video of Nexus in action; that is all you will need to see to realize why 20 years of dedication to corporate perfection pays dividends.
I will first show you the fundamental concepts behind a subquery in the examples. I will then show you a basic subquery. I will show you a join with two columns as the join condition. And finally, I will show you the elusive two-column subquery.
The example below is not a subquery but a simple in-list containing 100, 200, 300, 400, and 500. Notice the parenthesis. The result returns all employees in DEPT_NO 100, 200, 300, 400, or 500.
In the next example, I show you the first query above on the left but notice the equivalent subquery on the right. Instead of having the department numbers in parenthesis, I have run a subquery to return the department numbers from the department_table. The bottom query runs first, passing up to the top query department numbers 100, 200, 300, 400, and 500.
The example below shows two tables (SUBSCRIBERS and CLAIMS) which join together using both SUBSCRIBER_NO and MEMBER_NO. Subqueries are related to joins because both tables in a join or subquery will have a relationship that allows them to join or be part of a subquery. Our final example will use these two tables in a subquery.
In our final example, I show two equivalent queries. The first query is a join between the SUBSCRIBER and CLAIMS tables. The second query at the bottom shows a two-parameter subquery. Both examples return the same results.
The key to the bottom query is that the SUBSCRIBER_NO and MEMBER_NO columns are in parenthesis. In the bottom subquery, the SUBSCRIBER_NO and MEMBER_NO have no parenthesis but are merely separated by a comma. The parenthesis at the top and not at the bottom allows the elusive two-column subquery to work correctly.
Tom Coffing has also led a team of developers for almost 20 years to create the Nexus, which queries, migrates, and joins data across all systems. Amazingly, Nexus migrates between all the databases below with the click of the mouse.
• MySQL
• Excel
• Microsoft Access
• Snowflake
• Redshift
• Synapse
• BigQuery
• Teradata
• Oracle
• SQL Server
• DB2
• Hadoop
• MapR
• Greenplum
• Postgres
• Vertica
• Netezza
• SAP HANA
• Yellowbrick
• SQLite
The picture below shows Nexus in dark mode migrating SQL Server data to Yellowbrick. Some customers are migrating thousands of tables to Yellowbrick in a single job.
It is never too early to teach your children about data.
Comentários