Tag: SQL Advance

CROSS JOIN UNNEST in Presto

CROSS JOIN UNNEST in Presto

What is Presto and what is the meaning of un-nesting an array?
As the data grows, it becomes trickier to hold

CROSS JOIN UNNEST in Presto

CROSS JOIN UNNEST – It is simply used to flatten an array, flattening means converting an Array, Map or Row in a flat relation by converting it into multiple rows (one row for every value in array)
Sample table

NameEmp_idSubject(Array)Phone
X123[C,JAVA,SQL][123,456]
Y4231[Hive,Presto][542.654]
Z322[Ruby,Perl][12343]
Q421[Python,R][765,987]

Presto

Select Name,Emp_id,expertise
from Employee
CROSS JOIN UNNEST(Subject) as t(expertise)

If there are multiple arrays

Select Name,Emp_id,expertise,phone_num
from Employee
CROSS JOIN UNNEST(Subject,Phone) as t(expertise,phone_num)


Hive Query

Select Name,Emp_id,expertise
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise

If there are two columns to be unnested then

Select Name,Emp_id,expertise,Phone
from Employee
LATERAL VIEW explode(Subject) myTable1 as expertise
LATERAL VIEW explode(Phone) myTable2 as Phone


Knowing when and how to do unnesting is very important as there will be multiple instances where a tough problem can be solved using Cross Join Unnest.

We have also created a video on Cross Join unnest to explain it in a better way, Do check out the
Video Link Here

Now, if you are confused about starting SQL as a beginner and to reach to an interview ready state then do read this blog on How to prepare for SQL in 15 days

Do check out our booklist below for complete preparation in 30 Days

The Data Monk services

We are well known for our interview books and have 70+ e-book across Amazon and The Data Monk e-shop page . Following are best-seller combo packs and services that we are providing as of now

  1. YouTube channel covering all the interview-related important topics in SQL, Python, MS Excel, Machine Learning Algorithm, Statistics, and Direct Interview Questions
    Link – The Data Monk Youtube Channel
  2. Website – ~2000 completed solved Interview questions in SQL, Python, ML, and Case Study
    Link – The Data Monk website
  3. E-book shop – We have 70+ e-books available on our website and 3 bundles covering 2000+ solved interview questions. Do check it out
    Link – The Data E-shop Page
  4. Instagram Page – It covers only Most asked Questions and concepts (100+ posts). We have 100+ most asked interview topics explained in simple terms
    Link – The Data Monk Instagram page
  5. Mock Interviews/Career Guidance/Mentorship/Resume Making
    Book a slot on Top Mate

The Data Monk e-books

We know that each domain requires a different type of preparation, so we have divided our books in the same way:

1. 2200 Interview Questions to become Full Stack Analytics Professional – 2200 Most Asked Interview Questions
2.Data Scientist and Machine Learning Engineer -> 23 e-books covering all the ML Algorithms Interview Questions
3. 30 Days Analytics Course – Most Asked Interview Questions from 30 crucial topics

You can check out all the other e-books on our e-shop page – Do not miss it


For any information related to courses or e-books, please send an email to nitinkamal132@gmail.com