Skip to content

JOIN clause

The JOIN clause is used to combine rows from the database table and the model table on a related column. The basic syntax for joining from the data table and model is:

SELECT t.column_name1, t.column_name2, FROM integration_name.table AS t 
                                       JOIN mindsdb.predictor_name AS p WHERE t.column_name IN (value1, value2, ...);

JOIN example

The following SQL statement joins the home_rentals data with the home_rentals_model predicted price:

SELECT * FROM db_integration.house_rentals_data AS t JOIN mindsdb.home_rentals_model AS tb 
                                                       WHERE t.neighborhood in ('downtown', 'south_side');
number_of_rooms number_of_bathrooms sqft location days_on_market initial_price neighborhood rental_price select_data_query external_datasource when_data rental_price_original rental_price_confidence rental_price_explain rental_price_anomaly rental_price_min rental_price_max
0 1 484 great 10 2271 south_side 2271 nan nan nan nan 0.99 {"predicted_value": 2243, "confidence": 0.99, "confidence_lower_bound": 2200, "confidence_upper_bound": 2286, "anomaly": null, "truth": 2271} nan 2200 2286
1 1 674 good 1 2167 downtown 2167 nan nan nan nan 0.99 {"predicted_value": 2197, "confidence": 0.99, "confidence_lower_bound": 2154, "confidence_upper_bound": 2240, "anomaly": null, "truth": 2167} nan 2154 2240
0 1 529 great 3 2431 south_side 2431 nan nan nan nan 0.99 {"predicted_value": 2432, "confidence": 0.99, "confidence_lower_bound": 2389, "confidence_upper_bound": 2475, "anomaly": null, "truth": 2431} nan 2389 2475
3 2 1219 great 3 5510 south_side 5510 nan nan nan nan 0.99 {"predicted_value": 5550, "confidence": 0.99, "confidence_lower_bound": 5507, "confidence_upper_bound": 5593, "anomaly": null, "truth": 5510} nan 5507 5593
1 1 398 great 11 2272 south_side 2272 nan nan nan nan 0.99 {"predicted_value": 2252, "confidence": 0.99, "confidence_lower_bound": 2209, "confidence_upper_bound": 2295, "anomaly": null, "truth": 2272} nan 2209 2295