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 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 |