question

victoradityan-3322 avatar image
0 Votes"
victoradityan-3322 asked PRADEEPCHEEKATLA-MSFT commented

azure databricks table join returns no rows but query works for a single where predicate

I have two azure databricks tables,table one having 10 million rows and tabletwo having five thousand rows. there is common key - itemcode
The big table was created from parquet files and the other from Excel file using azure databricks GUI. Both table have columns as STRINGS.

when i run the query as -->

%sql
select * from tableone where itemcode = <some value> without quotes --- it returns the rows expected

but when i use a join as shown below it returns no rows, strange

select * from tableone join tabletwo on tableone.itemcode = tabletwo.itemcode

2) tried dataframe method same no results

sqlDF1 = spark.sql("SELECT * FROM tableone")

display(sqlDF1)

sqlDF1.printSchema()


sqlDF2 = spark.sql("SELECT * FROM tabletwo")

display(sqlDF2)

sqlDF2.printSchema()


df = sqlDF1.join(sqlDF2, on=['ItemBarcode'], how='inner')
display(df)

azure-databricks
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PRADEEPCHEEKATLA-MSFT avatar image
0 Votes"
PRADEEPCHEEKATLA-MSFT answered

Hello @victoradityan-3322,

Welcome to the Microsoft Q&A platform.

As per my repro, above two methods works as excepted. When you say it doesn't provide any result - could you please provide more detailed screenshot along with the sample data which you are trying and details about the databricks runtime version.

I had tested this with the sample data on databricks runtime version - 8.2 (includes Apache Spark 3.1.1, Scala 2.12)

Method1: To explain you in detail, I had created two tables named tableone as employee , tabletwo as department , and itemcode as deptno with the following data as shown below:

95892-image.png

Result of the method1 are as shown below:

Note: When you the join statement it will return deptno from both the tables.

95946-image.png

In order to avoid the deptno from both the tables, you need to use Inner Join as shown below:

 -- Use employee and department tables to demonstrate inner join.
 SELECT id, name, employee.deptno, deptname
     FROM employee INNER JOIN department ON employee.deptno = department.deptno;

95926-image.png

For more details, refer Azure Databricks - Joins examples.

Method2: Using dataframes, to explain you in detail, I had created two tables named tableone as employee , tabletwo as department , and itemcode as deptno with the following data as shown below:

95864-image.png

Result of the method2 are as shown below:

95857-image.png

Hope this helps. Do let us know if you any further queries.


Please don’t forget to Accept Answer and Up-Vote wherever the information provided helps you, this can be beneficial to other community members.


image.png (95.3 KiB)
image.png (91.9 KiB)
image.png (92.6 KiB)
image.png (99.4 KiB)
image.png (72.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

victoradityan-3322 avatar image
0 Votes"
victoradityan-3322 answered PRADEEPCHEEKATLA-MSFT commented

Hi Pradeep,

Thanks for the reply and uploading the pics. In fact I did follow the same steps as mentioned by you for both SQL & dataframes.

When joined it returns "no results" as shown below



96105-image.png


But when I try the same value against the table i get results as shown

96093-image.png



image.png (38.3 KiB)
image.png (55.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @victoradityan-3322,

Could you please you please share the code which you are running along with the clear screenshot?

0 Votes 0 ·

Hello @victoradityan-3322,

Just checking in if you have had a chance to see the previous response. We need the following information to understand/investigate this issue further.

0 Votes 0 ·