cross apply sql w3schools code example

Example 1: how to join tables in sql

JOINING 2 Tables in sql

SELECT X.Column_Name , Y.Column_Name2
FROM TABLES1_NAME X 
INNER JOIN TABLES2_NAME Y ON X.Primary_key = Y.Foreign_key;


--FOR EXAMPLE
--GET THE FIRST_NAME AND JOB_TITLE
--USE EMPLOYEES AND JOBS TABLE
--THE RELATIONSHIP IS JOB_ID

SELECT E.FIRST_NAME , J.JOB_TITLE
FROM EMPLOYEES E
INNER JOIN JOBS J ON J.JOB_ID = E.JOB_ID;

Example 2: userdefined function cross apply all days into the future sql

USE Northwind	
GO

SELECT o1.OrderID, o1.OrderDate, ca.OrderID AS NextOrder, 
		ca.OrderDate AS NextOrderDate, CustomerID, 
			DATEDIFF(DAY, o1.OrderDate,ca.OrderDate) DaysToNextOrder
FROM Orders AS o1
	CROSS APPLY
			(SELECT TOP 1 o.OrderDate, o.OrderID
			 FROM Orders AS o 
                         WHERE o.customerID = o1.customerID
				AND o.OrderID > o1.OrderID
			 ORDER BY OrderID) AS ca

ORDER BY CustomerID, o1.OrderID

Example 3: Apply functions to results of SQL queries.

# Apply functions to results of SQL queries.

results = spark.sql(
  "SELECT * FROM people")
names = results.map(lambda p: p.name)